alrahma_sunday_school_api/app/Services/Payments/PaymentManualService.php

<?php

namespace App\Services\Payments;

use App\Models\Configuration;
use App\Models\Invoice;
use App\Models\Payment;
use App\Models\Student;
use App\Services\Discounts\DiscountInvoiceService;
use Illuminate\Http\UploadedFile;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\Schema;

class PaymentManualService
{
    public function __construct(
        private DiscountInvoiceService $invoiceService,
        private PaymentEnrollmentEventService $enrollmentEventService
    ) {}

    public function search(string $searchTerm, ?string $schoolYear = null): array
    {
        $searchTerm = trim($searchTerm);
        $schoolYear = $schoolYear ?: Configuration::getConfig('school_year');
        $installmentDateRaw = (string) (Configuration::getConfig('installment_date') ?? '');

        $installmentEndYmd = '';
        if ($installmentDateRaw !== '') {
            $ts = strtotime($installmentDateRaw);
            if ($ts !== false) {
                $installmentEndYmd = date('Y-m-d', $ts);
            }
        }

        $parentData = null;
        $parentMatches = [];
        $students = [];
        $payments = [];
        $invoices = [];
        $pagination = null;

        if ($searchTerm !== '') {
            $searchClean = preg_replace('/\D/', '', $searchTerm);
            $searchFormatted = $this->formatPhone($searchClean);

            $builder = DB::table('users')->select('*');
            $builder->where(function ($q) use ($searchTerm, $searchClean, $searchFormatted) {
                $q->where('email', $searchTerm);

                if (ctype_digit($searchTerm)) {
                    $q->orWhere('id', (int) $searchTerm);
                }

                $q->orWhereRaw("CONCAT_WS(' ', firstname, lastname) LIKE ?", ['%'.$searchTerm.'%'])
                    ->orWhere('firstname', 'like', '%'.$searchTerm.'%')
                    ->orWhere('lastname', 'like', '%'.$searchTerm.'%');

                if (! empty($searchClean)) {
                    if (strlen($searchClean) === 10) {
                        $formattedPhone = substr($searchClean, 0, 3).'-'.substr($searchClean, 3, 3).'-'.substr($searchClean, 6, 4);
                        $q->orWhere('cellphone', $formattedPhone);
                    }

                    $q->orWhereRaw(
                        "REPLACE(REPLACE(REPLACE(REPLACE(cellphone, '(', ''), ')', ''), '-', ''), ' ', '') = ?",
                        [$searchClean]
                    );
                }

                if (! empty($searchFormatted)) {
                    $q->orWhere('cellphone', $searchFormatted);
                }
            })
                ->orderByRaw('LOWER(firstname)')
                ->orderByRaw('LOWER(lastname)')
                ->orderByRaw('LOWER(email)')
                ->limit(20);

            $parentRows = $builder->get()
                ->map(fn ($row) => $this->sanitizeParentRow((array) $row))
                ->all();
            $parentMatches = $parentRows;

            $parent = $parentRows[0] ?? [];
            if (! empty($parent)) {
                $parentData = $parent;
                $parentId = (int) ($parent['id'] ?? 0);

                if ($parentId > 0) {
                    $students = Student::query()->where('parent_id', $parentId)->get()->toArray();

                    $paymentPaginator = Payment::query()
                        ->where('parent_id', $parentId)
                        ->orderByDesc('payment_date')
                        ->paginate(10);

                    $payments = $paymentPaginator->items();
                    $pagination = [
                        'current_page' => $paymentPaginator->currentPage(),
                        'per_page' => $paymentPaginator->perPage(),
                        'total' => $paymentPaginator->total(),
                        'last_page' => $paymentPaginator->lastPage(),
                    ];

                    $rawInvoices = Invoice::query()
                        ->where('parent_id', $parentId)
                        ->when($schoolYear, fn ($q) => $q->where('school_year', $schoolYear))
                        ->orderByDesc('issue_date')
                        ->get()
                        ->toArray();

                    $invoices = $this->normalizeInvoices($rawInvoices, $installmentEndYmd);
                }
            }
        }

        return [
            'parent' => $parentData,
            'parent_matches' => $parentMatches,
            'students' => $students,
            'payments' => $payments,
            'invoices' => $invoices,
            'pagination' => $pagination,
            'search_term' => $searchTerm,
            'today' => function_exists('utc_now') ? utc_now() : now()->toDateTimeString(),
            'installment_end_ymd' => $installmentEndYmd,
        ];
    }

    public function suggest(string $query): array
    {
        $q = trim($query);
        if ($q === '') {
            return [];
        }

        $digits = preg_replace('/\D/', '', $q);

        $sql = "SELECT id, firstname, lastname, email, cellphone
                FROM users
                WHERE (
                    CONCAT_WS(' ', firstname, lastname) LIKE ?
                    OR email LIKE ?
                    OR cellphone LIKE ?";

        $params = ['%'.$q.'%', '%'.$q.'%', '%'.$q.'%'];

        if ($digits !== '') {
            $sql .= " OR REPLACE(REPLACE(REPLACE(REPLACE(cellphone, '(', ''), ')', ''), '-', ''), ' ', '') LIKE ?";
            $params[] = '%'.$digits.'%';
        }

        $sql .= ') ORDER BY LOWER(firstname), LOWER(lastname), LOWER(email) LIMIT 20';

        $rows = DB::select($sql, $params);

        $items = [];
        foreach ($rows as $row) {
            $label = trim(($row->firstname ?? '').' '.($row->lastname ?? ''));
            $email = trim((string) ($row->email ?? ''));
            $phone = trim((string) ($row->cellphone ?? ''));
            $sub = trim($email.' '.$phone);
            $value = $email !== '' ? $email : ($phone !== '' ? $phone : $label);

            $items[] = [
                'id' => (int) ($row->id ?? 0),
                'label' => $label,
                'sub' => $sub,
                'value' => $value,
            ];
        }

        return $items;
    }

    private function sanitizeParentRow(array $parent): array
    {
        unset($parent['password'], $parent['remember_token']);

        return $parent;
    }

    public function recordPayment(
        int $invoiceId,
        float $amount,
        string $paymentMethod,
        ?string $paymentDate,
        ?string $checkNumber,
        ?string $paymentType,
        ?UploadedFile $paymentFile,
        ?string $schoolYear = null,
        ?string $semester = null,
        ?int $userId = null
    ): array {
        $paymentMethod = strtolower(trim($paymentMethod));
        $allowedMethods = ['cash', 'check', 'card'];

        if (! in_array($paymentMethod, $allowedMethods, true)) {
            throw new \InvalidArgumentException('Invalid payment method.');
        }

        if ($paymentMethod === 'check' && ($checkNumber === null || $checkNumber === '')) {
            throw new \InvalidArgumentException('Check number is required for check payments.');
        }

        if ($amount <= 0) {
            throw new \InvalidArgumentException('Invalid amount.');
        }

        $invoice = Invoice::query()->find($invoiceId);
        if (! $invoice) {
            throw new \RuntimeException('Invoice not found.');
        }

        $schoolYear = $schoolYear ?: (string) ($invoice->school_year ?? Configuration::getConfig('school_year'));
        $semester = $semester ?: (string) ($invoice->semester ?? Configuration::getConfig('semester'));

        $initialPreBalance = $this->getCurrentInvoiceBalance($invoiceId, $schoolYear);

        if ($paymentMethod === 'card' && round($amount, 2) !== round($initialPreBalance, 2)) {
            throw new \InvalidArgumentException('Card payments must equal the full remaining balance.');
        }

        $checkFile = null;
        if ($paymentFile) {
            $checkFile = $this->storePaymentFile($paymentFile, $paymentMethod);
        }

        $paymentDate = $paymentDate ? date('Y-m-d H:i:s', strtotime($paymentDate)) : (function_exists('utc_now') ? utc_now() : now()->toDateTimeString());

        DB::beginTransaction();

        try {
            $locked = DB::table('invoices')->where('id', $invoiceId)->lockForUpdate()->first();
            if (! $locked) {
                DB::rollBack();
                throw new \RuntimeException('Invoice not found.');
            }

            $this->invoiceService->recalculateInvoice($invoiceId, $schoolYear);
            $currentBalance = $this->getCurrentInvoiceBalance($invoiceId, $schoolYear);

            if ($amount > $currentBalance + 0.00001) {
                DB::rollBack();
                throw new \InvalidArgumentException('Entered amount exceeds remaining balance.');
            }

            if ($paymentMethod === 'card' && round($amount, 2) !== round($currentBalance, 2)) {
                DB::rollBack();
                throw new \InvalidArgumentException('Card payments must equal the full remaining balance.');
            }

            $paidCount = $this->getSuccessfulPaymentCount($invoiceId);
            $installmentSeq = $paidCount + 1;

            $transactionId = 'INV-'.$invoiceId.'-'.str_replace('.', '', (string) microtime(true));

            $ok = $this->processPayment(
                $invoiceId,
                $amount,
                $paymentMethod,
                $checkFile,
                $transactionId,
                $paymentDate,
                $schoolYear,
                $semester,
                $checkNumber,
                $installmentSeq,
                $userId
            );

            if (! $ok) {
                DB::rollBack();
                throw new \RuntimeException('Failed to record payment.');
            }

            $this->invoiceService->recalculateInvoice($invoiceId, $schoolYear);

            $postBalance = max(0.0, round($initialPreBalance - $amount, 2));

            $enrollmentUpdated = $this->invoiceService->updateEnrollmentStatusIfPaid($invoiceId, $schoolYear);
            if ($enrollmentUpdated > 0) {
                $studentIds = Student::query()->where('parent_id', $invoice->parent_id)->pluck('id')->all();
                [$eventDataEnroll, $studentDataEnroll] = $this->enrollmentEventService->buildEventData(
                    (int) $invoice->parent_id,
                    $studentIds,
                    $schoolYear,
                    $semester
                );

                $this->triggerEvent('studentEnrolled', [$eventDataEnroll, $studentDataEnroll]);
            }

            $invoiceDiscount = (float) (DB::table('discount_usages')
                ->selectRaw('COALESCE(SUM(discount_amount),0) AS sum_disc')
                ->where('invoice_id', $invoiceId)
                ->value('sum_disc') ?? 0);

            $parentYearDiscountTotal = (float) (DB::table('discount_usages')
                ->selectRaw('COALESCE(SUM(discount_amount),0) AS sum_disc')
                ->where('parent_id', $invoice->parent_id)
                ->where('school_year', $schoolYear)
                ->value('sum_disc') ?? 0);

            DB::commit();

            [$eventData, $studentData] = $this->invoiceService->buildPaymentEventData(
                $invoiceId,
                $transactionId,
                $amount,
                $paymentMethod,
                $paymentDate,
                $checkNumber,
                $installmentSeq,
                $initialPreBalance,
                $postBalance,
                $schoolYear,
                $semester
            );

            $eventData['invoice_discount'] = $invoiceDiscount;
            $eventData['parent_year_discount_total'] = $parentYearDiscountTotal;

            $this->triggerEvent('paymentReceived', [$eventData, $studentData]);

            return [
                'transaction_id' => $transactionId,
                'installment_seq' => $installmentSeq,
                'pre_balance' => $initialPreBalance,
                'post_balance' => $postBalance,
            ];
        } catch (\Throwable $e) {
            DB::rollBack();
            Log::error('[manualPayUpdate] '.$e->getMessage());
            throw $e;
        }
    }

    public function editPayment(
        int $paymentId,
        float $paidAmount,
        string $paymentMethod,
        ?string $checkNumber,
        ?UploadedFile $paymentFile,
        ?int $userId = null
    ): void {
        $paymentMethod = strtolower(trim($paymentMethod));

        if ($paidAmount <= 0) {
            throw new \InvalidArgumentException('Invalid amount entered.');
        }

        $payment = Payment::query()->find($paymentId);
        if (! $payment) {
            throw new \RuntimeException('Original payment not found.');
        }

        $invoice = Invoice::query()->find((int) $payment->invoice_id);
        if (! $invoice) {
            throw new \RuntimeException('Linked invoice not found.');
        }

        $checkFile = $payment->check_file;
        if ($paymentFile) {
            $checkFile = $this->storePaymentFile($paymentFile, $paymentMethod);
        }

        $this->invoiceService->recalculateInvoice((int) $payment->invoice_id, (string) ($invoice->school_year ?? Configuration::getConfig('school_year')));

        $currentBalance = $this->getCurrentInvoiceBalanceExcludingPayment((int) $payment->invoice_id, $paymentId);

        if ($paidAmount > $currentBalance) {
            throw new \InvalidArgumentException('Entered amount exceeds remaining balance.');
        }

        $updateData = [
            'paid_amount' => $paidAmount,
            'payment_method' => $paymentMethod,
            'check_file' => $checkFile,
            'updated_by' => $userId,
            'check_number' => $paymentMethod === 'check' ? $checkNumber : null,
        ];

        Payment::query()->whereKey($paymentId)->update($updateData);

        $this->invoiceService->recalculateInvoice((int) $payment->invoice_id, (string) ($invoice->school_year ?? Configuration::getConfig('school_year')));
    }

    public function serveCheckFile(string $filename, string $mode = 'download')
    {
        $filename = basename($filename);
        $roots = [
            storage_path('app/uploads/checks/'.$filename),
            storage_path('app/uploads/cards/'.$filename),
            storage_path('app/uploads/misc/'.$filename),
            storage_path('app/uploads/'.$filename),
        ];

        $path = null;
        foreach ($roots as $candidate) {
            if (is_file($candidate)) {
                $path = $candidate;
                break;
            }
        }

        if (! $path) {
            abort(404, 'Payment file not found.');
        }

        if ($mode === 'inline') {
            return response()->file($path, [
                'Content-Type' => mime_content_type($path) ?: 'application/octet-stream',
                'Content-Disposition' => 'inline; filename="'.$filename.'"',
            ]);
        }

        return response()->download($path, $filename);
    }

    private function processPayment(
        int $invoiceId,
        float $amount,
        string $paymentMethod,
        ?string $checkFile,
        string $transactionId,
        string $paymentDate,
        ?string $schoolYear,
        ?string $semester,
        ?string $checkNumber,
        int $installmentSeq,
        ?int $userId
    ): bool {
        $invoice = Invoice::query()->find($invoiceId);
        if (! $invoice) {
            return false;
        }

        $newPaid = (float) $invoice->paid_amount + $amount;
        $newBalance = (float) $invoice->balance - $amount;

        if ($newBalance == $invoice->total_amount) {
            $paymentStatus = 'Unpaid';
        } elseif ($newBalance > 0 && $newBalance < $invoice->total_amount) {
            $paymentStatus = 'Partially Paid';
        } elseif ($newBalance <= 0.00001) {
            $paymentStatus = 'Paid';
        } else {
            $paymentStatus = $invoice->status;
        }

        $invoiceUpdateData = [
            'paid_amount' => $newPaid,
            'balance' => $newBalance,
            'status' => $paymentStatus,
            'updated_by' => $userId,
        ];

        if (! Invoice::query()->whereKey($invoiceId)->update($invoiceUpdateData)) {
            return false;
        }

        $paymentData = [
            'parent_id' => $invoice->parent_id,
            'invoice_id' => $invoiceId,
            'total_amount' => $invoice->total_amount,
            'paid_amount' => $amount,
            'balance' => $newBalance,
            'number_of_installments' => $installmentSeq,
            'transaction_id' => $transactionId,
            'payment_method' => strtolower($paymentMethod),
            'payment_date' => $paymentDate,
            'status' => $paymentStatus,
            'check_file' => $checkFile,
            'check_number' => strtolower($paymentMethod) === 'check' ? $checkNumber : null,
            'updated_by' => $userId,
            'school_year' => $schoolYear,
            'semester' => $semester,
        ];

        return (bool) Payment::query()->create($paymentData);
    }

    private function getCurrentInvoiceBalance(int $invoiceId, string $schoolYear): float
    {
        return $this->invoiceService->getCurrentInvoiceBalance($invoiceId, $schoolYear);
    }

    private function getCurrentInvoiceBalanceExcludingPayment(int $invoiceId, int $excludePaymentId): float
    {
        $invoice = Invoice::query()->find($invoiceId);
        if (! $invoice) {
            return 0.0;
        }

        $paymentQuery = Payment::query()
            ->selectRaw('COALESCE(SUM(paid_amount),0) AS total_paid')
            ->where('invoice_id', $invoiceId)
            ->where('id', '!=', $excludePaymentId);

        if (Schema::hasColumn('payments', 'status')) {
            $paymentQuery->where(function ($q) {
                $q->whereNotIn('status', [
                    'void', 'voided', 'refunded', 'failed', 'chargeback', 'declined', 'reversed', 'canceled', 'cancelled',
                ])->orWhereNull('status');
            });
        }

        if (Schema::hasColumn('payments', 'is_void')) {
            $paymentQuery->where(function ($q) {
                $q->where('is_void', 0)->orWhereNull('is_void');
            });
        }

        $row = (array) $paymentQuery->first();
        $totalPaid = (float) ($row['total_paid'] ?? 0);

        $totalDisc = (float) (DB::table('discount_usages')
            ->selectRaw('COALESCE(SUM(discount_amount),0) AS total_disc')
            ->where('invoice_id', $invoiceId)
            ->value('total_disc') ?? 0);

        $totalRefundPaid = (float) (DB::table('refunds')
            ->selectRaw('COALESCE(SUM(refund_paid_amount),0) AS total_refund_paid')
            ->where('invoice_id', $invoiceId)
            ->whereIn('status', ['Partial', 'Paid'])
            ->value('total_refund_paid') ?? 0);

        $total = (float) ($invoice->total_amount ?? 0);

        return max(0.0, round($total - $totalPaid - $totalDisc - $totalRefundPaid, 2));
    }

    private function getSuccessfulPaymentCount(int $invoiceId): int
    {
        $exclude = ['void', 'voided', 'refunded', 'failed', 'chargeback', 'declined', 'reversed', 'canceled', 'cancelled'];

        return Payment::query()
            ->where('invoice_id', $invoiceId)
            ->where('paid_amount', '>', 0)
            ->whereNotIn('status', $exclude)
            ->count();
    }

    private function normalizeInvoices(array $rawInvoices, string $installmentEndYmd): array
    {
        if (empty($rawInvoices)) {
            return [];
        }

        $invoiceIds = array_values(array_unique(array_map(static fn ($r) => (int) ($r['id'] ?? 0), $rawInvoices)));
        $paidByInvoice = [];
        $discountByInvoice = [];
        $refundByInvoice = [];

        if (! empty($invoiceIds)) {
            $rows = Payment::query()
                ->selectRaw('invoice_id, COALESCE(SUM(paid_amount),0) AS total_paid')
                ->whereIn('invoice_id', $invoiceIds)
                ->groupBy('invoice_id')
                ->get()
                ->map(fn ($row) => (array) $row)
                ->all();

            foreach ($rows as $row) {
                $paidByInvoice[(int) ($row['invoice_id'] ?? 0)] = (float) ($row['total_paid'] ?? 0);
            }

            $discRows = DB::table('discount_usages')
                ->selectRaw('invoice_id, COALESCE(SUM(discount_amount),0) AS total_disc')
                ->whereIn('invoice_id', $invoiceIds)
                ->groupBy('invoice_id')
                ->get()
                ->map(fn ($row) => (array) $row)
                ->all();

            foreach ($discRows as $row) {
                $discountByInvoice[(int) ($row['invoice_id'] ?? 0)] = (float) ($row['total_disc'] ?? 0);
            }

            $refundRows = DB::table('refunds')
                ->selectRaw('invoice_id, COALESCE(SUM(refund_paid_amount),0) AS total_refund_paid')
                ->whereIn('invoice_id', $invoiceIds)
                ->whereIn('status', ['Partial', 'Paid'])
                ->groupBy('invoice_id')
                ->get()
                ->map(fn ($row) => (array) $row)
                ->all();

            foreach ($refundRows as $row) {
                $refundByInvoice[(int) ($row['invoice_id'] ?? 0)] = (float) ($row['total_refund_paid'] ?? 0);
            }
        }

        return array_map(function (array $inv) use ($installmentEndYmd, $paidByInvoice, $discountByInvoice, $refundByInvoice) {
            $inv['total_amount'] = isset($inv['total_amount']) ? (float) $inv['total_amount'] : 0.0;
            $iid = (int) ($inv['id'] ?? 0);
            $actualPaid = $paidByInvoice[$iid] ?? null;
            $inv['paid_amount'] = is_numeric($actualPaid) ? (float) $actualPaid : (float) ($inv['paid_amount'] ?? 0);
            $inv['discount'] = isset($discountByInvoice[$iid]) ? (float) $discountByInvoice[$iid] : (float) ($inv['discount'] ?? 0.0);
            $inv['refund_paid'] = isset($refundByInvoice[$iid]) ? (float) $refundByInvoice[$iid] : 0.0;
            $inv['balance'] = max(0.0, (float) $inv['total_amount'] - (float) $inv['paid_amount'] - (float) $inv['discount'] - (float) $inv['refund_paid']);
            $inv['due_ymd'] = $installmentEndYmd;

            $issueYmd = '';
            foreach (['issue_date', 'start_date', 'created_at'] as $k) {
                if (! empty($inv[$k])) {
                    $its = strtotime((string) $inv[$k]);
                    if ($its !== false) {
                        $issueYmd = date('Y-m-d', $its);
                        break;
                    }
                }
            }
            $inv['issue_ymd'] = $issueYmd;

            return $inv;
        }, $rawInvoices);
    }

    private function formatPhone(string $input): string
    {
        $digits = preg_replace('/\D/', '', $input);
        if (strlen($digits) !== 10) {
            return $input;
        }

        return sprintf('(%s)-%s-%s', substr($digits, 0, 3), substr($digits, 3, 3), substr($digits, 6, 4));
    }

    private function storePaymentFile(UploadedFile $file, string $paymentMethod): string
    {
        $mime = $file->getMimeType();
        $ext = strtolower($file->getClientOriginalExtension() ?: '');

        $okTypes = ['image/jpeg', 'image/png', 'application/pdf'];
        $okExts = ['jpg', 'jpeg', 'png', 'pdf'];

        if (! in_array($mime, $okTypes, true) || ! in_array($ext, $okExts, true)) {
            throw new \InvalidArgumentException('Unsupported file type. Use JPG, PNG, or PDF.');
        }

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

        $fileName = $file->hashName();
        $subdir = $paymentMethod === 'check' ? 'checks' : ($paymentMethod === 'card' ? 'cards' : 'misc');
        $targetDir = storage_path('app/uploads/'.$subdir);

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

        $file->move($targetDir, $fileName);

        return $fileName;
    }

    private function triggerEvent(string $name, array $payload): void
    {
        try {
            if (function_exists('event')) {
                event($name, $payload);
            }
        } catch (\Throwable $e) {
            Log::warning($name.' hook failed: '.$e->getMessage());
        }
    }
}