<?php
declare(strict_types=1);

namespace App\Modules\Members\Services;

use App\Core\App;
use App\Core\EventBus;
use App\Core\Logger;

final class RetroactiveMembershipService
{
    public static function createRetroactiveMember(array $data): array
    {
        $db = App::getInstance()->db();
        $employee = App::getInstance()->currentEmployee();
        $empId = $employee ? (int) $employee->id : null;
        $ts = date('Y-m-d H:i:s');

        $db->beginTransaction();
        try {
            // Step 1: Create the member record with retroactive dates
            $formNumber = (string) (MemberNumberGenerator::next() ?? 1);
            $joinDate = $data['join_date'] ?? date('Y-m-d');
            $formDate = $data['form_date'] ?? $joinDate;

            $nationalId = self::emptyToNull($data['national_id'] ?? null);
            $memberData = [
                'full_name_ar'       => $data['full_name_ar'],
                'full_name_en'       => self::emptyToNull($data['full_name_en'] ?? null),
                'national_id'        => $nationalId,
                'passport_number'    => self::emptyToNull($data['passport_number'] ?? null),
                'id_type'            => $nationalId ? 'national_id' : 'passport',
                'date_of_birth'      => self::emptyToNull($data['date_of_birth'] ?? null),
                'gender'             => $data['gender'] ?? 'male',
                'phone_mobile'       => self::emptyToNull($data['phone_mobile'] ?? null),
                'phone_home'         => self::emptyToNull($data['phone_home'] ?? null),
                'email'              => self::emptyToNull($data['email'] ?? null),
                'branch_id'          => (int) ($data['branch_id'] ?? 1),
                'membership_type'    => $data['membership_type'] ?? 'working',
                'member_category'    => $data['member_category'] ?? 'working_member',
                'nationality'        => $data['nationality'] ?? 'egyptian',
                'form_number'        => $formNumber,
                'form_date'          => $formDate,
                'status'             => 'active',
                'membership_value'   => $data['membership_value'] ?? '0.00',
                'qualification_id'   => isset($data['qualification_id']) && $data['qualification_id'] ? (int) $data['qualification_id'] : null,
                'occupation'         => self::emptyToNull($data['occupation'] ?? null),
                'residence_address'  => self::emptyToNull($data['residence_address'] ?? null),
                'area'               => self::emptyToNull($data['area'] ?? null),
                'governorate'        => self::emptyToNull($data['governorate'] ?? null),
                'payment_method'     => $data['payment_method'] ?? 'cash',
                'discount_amount'    => $data['discount_amount'] ?? '0.00',
            ];

            $memberId = $db->insert('members', array_merge($memberData, [
                'created_at' => $formDate . ' 09:00:00',
                'updated_at' => $ts,
                'created_by' => $empId,
            ]));

            // Assign membership number (use provided one or auto-generate)
            if (!empty($data['membership_number'])) {
                $membershipNumber = $data['membership_number'];
                $db->update('members', [
                    'membership_number' => $membershipNumber,
                    'updated_at'        => date('Y-m-d H:i:s'),
                ], '`id` = ?', [$memberId]);
            } else {
                $membershipNumber = MemberNumberGenerator::assign($memberId);
            }

            // Update activated_at to the join date
            $db->update('members', [
                'activated_at'           => $joinDate . ' 09:00:00',
                'activated_by_payment_id' => 0, // placeholder, updated after payment
            ], '`id` = ?', [$memberId]);

            $result = [
                'member_id'         => $memberId,
                'membership_number' => $membershipNumber,
                'payments'          => [],
                'subscriptions'     => [],
                'installments'      => null,
                'fines'             => [],
                'dependents'        => [],
            ];

            // Step 2: Create form fee payment (retroactive)
            if (!empty($data['form_fee_amount']) && bccomp($data['form_fee_amount'], '0', 2) > 0) {
                $paymentId = self::createRetroactivePayment($memberId, [
                    'payment_type'   => 'form_fee',
                    'amount'         => $data['form_fee_amount'],
                    'payment_method' => $data['form_fee_method'] ?? 'cash',
                    'payment_date'   => $formDate,
                    'check_number'   => $data['form_fee_check_number'] ?? null,
                    'check_bank'     => $data['form_fee_check_bank'] ?? null,
                    'check_date'     => $data['form_fee_check_date'] ?? null,
                    'description'    => 'رسوم استمارة — إدخال بأثر رجعي',
                ]);
                $result['payments'][] = ['type' => 'form_fee', 'id' => $paymentId, 'date' => $formDate];
            }

            // Step 3: Create membership fee payment (retroactive)
            $membershipPaymentId = null;
            if (!empty($data['membership_value']) && bccomp($data['membership_value'], '0', 2) > 0) {
                $paymentDate = $data['membership_payment_date'] ?? $joinDate;
                $paymentMethod = $data['membership_payment_method'] ?? 'cash';

                if ($paymentMethod === 'installment' && !empty($data['installment_plan'])) {
                    // Down payment
                    $downPayment = $data['installment_plan']['down_payment'] ?? '0.00';
                    $downPaymentDate = $data['installment_plan']['start_date'] ?? $joinDate;

                    $membershipPaymentId = self::createRetroactivePayment($memberId, [
                        'payment_type'   => 'down_payment',
                        'amount'         => $downPayment,
                        'payment_method' => $data['installment_plan']['down_payment_method'] ?? 'cash',
                        'payment_date'   => $downPaymentDate,
                        'check_number'   => $data['installment_plan']['down_check_number'] ?? null,
                        'check_bank'     => $data['installment_plan']['down_check_bank'] ?? null,
                        'check_date'     => $data['installment_plan']['down_check_date'] ?? null,
                        'description'    => 'مقدم تقسيط — إدخال بأثر رجعي',
                    ]);
                    $result['payments'][] = ['type' => 'down_payment', 'id' => $membershipPaymentId, 'date' => $downPaymentDate];

                    // Create installment plan
                    $installmentResult = self::createRetroactiveInstallmentPlan($memberId, $data['installment_plan'], $membershipPaymentId);
                    $result['installments'] = $installmentResult;
                } else {
                    // Full payment (cash/check/visa/transfer)
                    $membershipPaymentId = self::createRetroactivePayment($memberId, [
                        'payment_type'   => 'membership_fee',
                        'amount'         => $data['membership_value'],
                        'payment_method' => $paymentMethod,
                        'payment_date'   => $paymentDate,
                        'check_number'   => $data['membership_check_number'] ?? null,
                        'check_bank'     => $data['membership_check_bank'] ?? null,
                        'check_date'     => $data['membership_check_date'] ?? null,
                        'visa_reference' => $data['membership_visa_reference'] ?? null,
                        'transfer_reference' => $data['membership_transfer_reference'] ?? null,
                        'transfer_bank'  => $data['membership_transfer_bank'] ?? null,
                        'description'    => 'قيمة العضوية — إدخال بأثر رجعي',
                    ]);
                    $result['payments'][] = ['type' => 'membership_fee', 'id' => $membershipPaymentId, 'date' => $paymentDate];
                }

                // Update activated_by_payment_id
                $db->update('members', [
                    'activated_by_payment_id' => $membershipPaymentId,
                ], '`id` = ?', [$memberId]);
            }

            // Step 4: Create dependents
            if (!empty($data['spouses'])) {
                foreach ($data['spouses'] as $spouse) {
                    $spouseJoinDate = self::emptyToNull($spouse['join_date'] ?? null) ?? $joinDate;
                    $spouseId = $db->insert('spouses', [
                        'member_id'       => $memberId,
                        'full_name_ar'    => $spouse['full_name_ar'],
                        'national_id'     => self::emptyToNull($spouse['national_id'] ?? null),
                        'date_of_birth'   => self::emptyToNull($spouse['date_of_birth'] ?? null),
                        'marriage_date'   => self::emptyToNull($spouse['marriage_date'] ?? null) ?? $joinDate,
                        'phone_mobile'    => self::emptyToNull($spouse['phone_mobile'] ?? null),
                        'status'          => 'active',
                        'join_date'       => $spouseJoinDate,
                        'addition_fee'    => $spouse['addition_fee'] ?? '0.00',
                        'activated_by_payment_id' => $membershipPaymentId,
                        'is_archived'     => 0,
                        'created_at'      => $spouseJoinDate . ' 09:00:00',
                        'updated_at'      => $ts,
                    ]);

                    if (!empty($spouse['addition_fee']) && bccomp($spouse['addition_fee'], '0', 2) > 0 && !empty($spouse['has_separate_fee'])) {
                        $spPayId = self::createRetroactivePayment($memberId, [
                            'payment_type'        => 'addition_fee',
                            'amount'              => $spouse['addition_fee'],
                            'payment_method'      => $spouse['payment_method'] ?? 'cash',
                            'payment_date'        => $spouse['payment_date'] ?? $joinDate,
                            'related_entity_type' => 'spouses',
                            'related_entity_id'   => $spouseId,
                            'description'         => 'رسوم إضافة زوجة — إدخال بأثر رجعي',
                        ]);
                        $db->update('spouses', ['activated_by_payment_id' => $spPayId], '`id` = ?', [$spouseId]);
                    }

                    $result['dependents'][] = ['type' => 'spouse', 'id' => $spouseId, 'name' => $spouse['full_name_ar']];
                }
            }

            if (!empty($data['children'])) {
                foreach ($data['children'] as $child) {
                    $childJoinDate = self::emptyToNull($child['join_date'] ?? null) ?? $joinDate;
                    $childId = $db->insert('children', [
                        'member_id'       => $memberId,
                        'full_name_ar'    => $child['full_name_ar'],
                        'national_id'     => self::emptyToNull($child['national_id'] ?? null),
                        'date_of_birth'   => self::emptyToNull($child['date_of_birth'] ?? null),
                        'gender'          => $child['gender'] ?? 'male',
                        'status'          => 'active',
                        'join_date'       => $childJoinDate,
                        'addition_fee'    => $child['addition_fee'] ?? '0.00',
                        'activated_by_payment_id' => $membershipPaymentId,
                        'is_archived'     => 0,
                        'created_at'      => $childJoinDate . ' 09:00:00',
                        'updated_at'      => $ts,
                    ]);

                    if (!empty($child['addition_fee']) && bccomp($child['addition_fee'], '0', 2) > 0 && !empty($child['has_separate_fee'])) {
                        $chPayId = self::createRetroactivePayment($memberId, [
                            'payment_type'        => 'addition_fee',
                            'amount'              => $child['addition_fee'],
                            'payment_method'      => $child['payment_method'] ?? 'cash',
                            'payment_date'        => $child['payment_date'] ?? $joinDate,
                            'related_entity_type' => 'children',
                            'related_entity_id'   => $childId,
                            'description'         => 'رسوم إضافة ابن — إدخال بأثر رجعي',
                        ]);
                        $db->update('children', ['activated_by_payment_id' => $chPayId], '`id` = ?', [$childId]);
                    }

                    $result['dependents'][] = ['type' => 'child', 'id' => $childId, 'name' => $child['full_name_ar']];
                }
            }

            // Step 5: Create retroactive subscriptions
            if (!empty($data['subscriptions'])) {
                foreach ($data['subscriptions'] as $sub) {
                    $subId = self::createRetroactiveSubscription($memberId, $sub);
                    $result['subscriptions'][] = $subId;
                }
            }

            // Step 6: Create retroactive fines/violations
            if (!empty($data['violations'])) {
                foreach ($data['violations'] as $violation) {
                    $fineResult = self::createRetroactiveViolation($memberId, $violation);
                    $result['fines'][] = $fineResult;
                }
            }

            // Step 7: Handle renewals — mark subscription years as paid or overdue
            if (!empty($data['renewal_history'])) {
                foreach ($data['renewal_history'] as $renewal) {
                    self::processRetroactiveRenewal($memberId, $renewal);
                }
            }

            $db->commit();

            EventBus::dispatch('member.created', ['member_id' => $memberId, 'retroactive' => true]);
            Logger::info("Retroactive member created", ['member_id' => $memberId, 'join_date' => $joinDate]);

            return ['success' => true, 'data' => $result];

        } catch (\Throwable $e) {
            $db->rollBack();
            Logger::error("Retroactive member creation failed: " . $e->getMessage(), ['trace' => $e->getTraceAsString()]);
            return ['success' => false, 'error' => $e->getMessage()];
        }
    }

    private static function emptyToNull(?string $value): ?string
    {
        return ($value !== null && trim($value) !== '') ? trim($value) : null;
    }

    private static function createRetroactivePayment(int $memberId, array $data): int
    {
        $db = App::getInstance()->db();
        $employee = App::getInstance()->currentEmployee();
        $empId = $employee ? (int) $employee->id : null;
        $paymentDate = self::emptyToNull($data['payment_date'] ?? null) ?? date('Y-m-d');

        $receiptNumber = self::generateRetroReceiptNumber($paymentDate);

        $paymentId = $db->insert('payments', [
            'member_id'               => $memberId,
            'payment_type'            => $data['payment_type'],
            'amount'                  => $data['amount'],
            'currency'                => 'EGP',
            'payment_method'          => $data['payment_method'] ?? 'cash',
            'check_number'            => self::emptyToNull($data['check_number'] ?? null),
            'check_bank'              => self::emptyToNull($data['check_bank'] ?? null),
            'check_date'              => self::emptyToNull($data['check_date'] ?? null),
            'check_status'            => ($data['payment_method'] ?? 'cash') === 'check' ? 'cleared' : null,
            'visa_reference'          => self::emptyToNull($data['visa_reference'] ?? null),
            'transfer_reference'      => self::emptyToNull($data['transfer_reference'] ?? null),
            'transfer_bank'           => self::emptyToNull($data['transfer_bank'] ?? null),
            'related_entity_type'     => self::emptyToNull($data['related_entity_type'] ?? null),
            'related_entity_id'       => $data['related_entity_id'] ?? null,
            'notes'                   => ($data['description'] ?? '') . ' [RETROACTIVE]',
            'payment_date'            => $paymentDate,
            'received_by_employee_id' => $empId,
            'is_voided'               => 0,
            'created_at'              => $paymentDate . ' 09:00:00',
            'updated_at'              => date('Y-m-d H:i:s'),
            'created_by'              => $empId,
        ]);

        $receiptId = $db->insert('receipts', [
            'receipt_number'        => $receiptNumber,
            'member_id'             => $memberId,
            'payment_id'            => $paymentId,
            'receipt_type'          => 'payment',
            'amount'                => $data['amount'],
            'amount_in_words_ar'    => '',
            'description_ar'        => $data['description'] ?? 'دفعة بأثر رجعي',
            'issued_by_employee_id' => $empId,
            'issued_at'             => $paymentDate . ' 09:00:00',
            'is_voided'             => 0,
            'print_count'           => 0,
            'created_at'            => $paymentDate . ' 09:00:00',
        ]);

        $db->update('payments', ['receipt_id' => $receiptId], '`id` = ?', [$paymentId]);

        return $paymentId;
    }

    private static function createRetroactiveInstallmentPlan(int $memberId, array $plan, int $downPaymentId): array
    {
        $db = App::getInstance()->db();
        $employee = App::getInstance()->currentEmployee();
        $empId = $employee ? (int) $employee->id : null;

        $totalAmount = $plan['total_amount'];
        $downPayment = $plan['down_payment'];
        $months = (int) ($plan['months'] ?? 12);
        $startDate = $plan['start_date'] ?? date('Y-m-d');
        $interestRate = $plan['interest_rate'] ?? '22.00';

        $remaining = bcsub($totalAmount, $downPayment, 2);
        $monthlyRate = bcdiv($interestRate, '1200', 10);
        $monthlyPrincipal = bcdiv($remaining, (string) $months, 2);

        $totalInterest = '0.00';
        $runningBalance = $remaining;
        $schedule = [];

        for ($i = 1; $i <= $months; $i++) {
            $interest = bcmul($runningBalance, $monthlyRate, 2);
            $totalInterest = bcadd($totalInterest, $interest, 2);
            $principal = ($i === $months) ? $runningBalance : $monthlyPrincipal;
            $amount = bcadd($principal, $interest, 2);
            $runningBalance = bcsub($runningBalance, $principal, 2);
            if (bccomp($runningBalance, '0', 2) < 0) $runningBalance = '0.00';

            $dueDate = date('Y-m-d', strtotime($startDate . " +{$i} months"));
            $schedule[] = [
                'number'   => $i,
                'due_date' => $dueDate,
                'amount'   => $amount,
                'principal'=> $principal,
                'interest' => $interest,
                'remaining'=> $runningBalance,
            ];
        }

        $totalWithInterest = bcadd($remaining, $totalInterest, 2);
        $avgMonthly = bcdiv($totalWithInterest, (string) $months, 2);

        $planId = $db->insert('installment_plans', [
            'member_id'            => $memberId,
            'related_entity_type'  => 'members',
            'related_entity_id'    => $memberId,
            'total_amount'         => $totalAmount,
            'down_payment'         => $downPayment,
            'remaining_balance'    => $remaining,
            'interest_rate'        => $interestRate,
            'total_interest'       => $totalInterest,
            'total_with_interest'  => $totalWithInterest,
            'number_of_months'     => $months,
            'monthly_payment'      => $avgMonthly,
            'start_date'           => $startDate,
            'down_payment_receipt'  => $downPaymentId,
            'status'               => 'active',
            'created_at'           => $startDate . ' 09:00:00',
            'updated_at'           => date('Y-m-d H:i:s'),
            'created_by'           => $empId,
        ]);

        // Create schedule items and process paid ones
        $paidInstallments = $plan['paid_installments'] ?? [];
        $paidCount = 0;

        foreach ($schedule as $item) {
            $isPaid = false;
            $paidAt = null;
            $paymentId = null;

            foreach ($paidInstallments as $paid) {
                if ((int) $paid['number'] === $item['number']) {
                    $isPaid = true;
                    $paidAt = $paid['paid_date'] ?? $item['due_date'];
                    break;
                }
            }

            $status = 'pending';
            if ($isPaid) {
                $status = 'paid';
                $paidCount++;
                $paymentId = self::createRetroactivePayment($memberId, [
                    'payment_type'   => 'installment',
                    'amount'         => $item['amount'],
                    'payment_method' => $paid['payment_method'] ?? 'cash',
                    'payment_date'   => $paidAt,
                    'check_number'   => $paid['check_number'] ?? null,
                    'check_bank'     => $paid['check_bank'] ?? null,
                    'check_date'     => $paid['check_date'] ?? null,
                    'description'    => "قسط رقم {$item['number']} — إدخال بأثر رجعي",
                ]);
            } elseif (strtotime($item['due_date']) < time()) {
                $status = 'overdue';
            }

            $db->insert('installment_schedule', [
                'installment_plan_id' => $planId,
                'installment_number'  => $item['number'],
                'due_date'            => $item['due_date'],
                'amount'              => $item['amount'],
                'principal'           => $item['principal'],
                'interest'            => $item['interest'],
                'remaining_after'     => $item['remaining'],
                'paid_amount'         => $isPaid ? $item['amount'] : '0.00',
                'payment_id'          => $paymentId,
                'status'              => $status,
                'paid_at'             => $paidAt,
                'created_at'          => $startDate . ' 09:00:00',
                'updated_at'          => date('Y-m-d H:i:s'),
            ]);
        }

        // Create cheques if provided
        if (!empty($plan['cheques'])) {
            foreach ($plan['cheques'] as $cheque) {
                $db->insert('installment_cheques', [
                    'installment_plan_id' => $planId,
                    'cheque_number'       => $cheque['cheque_number'],
                    'bank_name'           => $cheque['bank_name'],
                    'cheque_date'         => $cheque['cheque_date'],
                    'cheque_amount'       => $cheque['cheque_amount'],
                    'uploaded_by'         => $empId,
                    'notes'               => 'شيك بأثر رجعي',
                    'created_at'          => date('Y-m-d H:i:s'),
                ]);
            }
        }

        // If all paid, mark plan as completed
        if ($paidCount >= $months) {
            $db->update('installment_plans', ['status' => 'completed', 'updated_at' => date('Y-m-d H:i:s')], '`id` = ?', [$planId]);
        }

        return [
            'plan_id'    => $planId,
            'months'     => $months,
            'paid_count' => $paidCount,
            'status'     => $paidCount >= $months ? 'completed' : 'active',
        ];
    }

    private static function createRetroactiveSubscription(int $memberId, array $sub): int
    {
        $db = App::getInstance()->db();
        $employee = App::getInstance()->currentEmployee();
        $empId = $employee ? (int) $employee->id : null;

        $status = $sub['status'] ?? 'pending';
        $paidAmount = $sub['paid_amount'] ?? '0.00';
        $paymentId = null;

        if ($status === 'paid' && bccomp($paidAmount, '0', 2) > 0) {
            $paymentId = self::createRetroactivePayment($memberId, [
                'payment_type'   => 'annual_subscription',
                'amount'         => $paidAmount,
                'payment_method' => $sub['payment_method'] ?? 'cash',
                'payment_date'   => $sub['paid_date'] ?? date('Y-m-d'),
                'description'    => "اشتراك سنوي {$sub['financial_year']} — إدخال بأثر رجعي",
            ]);
        }

        $subId = $db->insert('subscriptions', [
            'member_id'       => $memberId,
            'financial_year'  => $sub['financial_year'],
            'person_type'     => $sub['person_type'] ?? 'member',
            'person_id'       => $memberId,
            'person_name'     => $sub['person_name'] ?? '',
            'base_amount'     => $sub['base_amount'] ?? '0.00',
            'development_fee' => $sub['development_fee'] ?? '35.00',
            'discount_amount' => $sub['discount_amount'] ?? '0.00',
            'total_amount'    => $sub['total_amount'] ?? '0.00',
            'paid_amount'     => $paidAmount,
            'fine_amount'     => $sub['fine_amount'] ?? '0.00',
            'status'          => $status,
            'payment_id'      => $paymentId,
            'paid_at'         => ($status === 'paid') ? (self::emptyToNull($sub['paid_date'] ?? null) ?? date('Y-m-d H:i:s')) : null,
            'created_at'      => ($sub['financial_year'] ?? date('Y')) . '-01-01 09:00:00',
            'updated_at'      => date('Y-m-d H:i:s'),
            'created_by'      => $empId,
        ]);

        return $subId;
    }

    private static function createRetroactiveViolation(int $memberId, array $violation): array
    {
        $db = App::getInstance()->db();
        $employee = App::getInstance()->currentEmployee();
        $empId = $employee ? (int) $employee->id : null;

        $violationDate = self::emptyToNull($violation['violation_date'] ?? null) ?? date('Y-m-d');
        $violationId = $db->insert('violations', [
            'member_id'       => $memberId,
            'violation_date'  => $violationDate,
            'description'     => $violation['description'] ?? 'مخالفة مسجلة بأثر رجعي',
            'reported_by'     => $empId,
            'evidence_notes'  => self::emptyToNull($violation['evidence_notes'] ?? null),
            'status'          => 'confirmed',
            'created_at'      => $violationDate . ' 09:00:00',
            'updated_at'      => date('Y-m-d H:i:s'),
        ]);

        $fineStatus = $violation['fine_status'] ?? 'imposed';
        $finePaymentId = null;

        if ($fineStatus === 'paid' && !empty($violation['fine_amount'])) {
            $finePaymentId = self::createRetroactivePayment($memberId, [
                'payment_type'   => 'fine',
                'amount'         => $violation['fine_amount'],
                'payment_method' => $violation['fine_payment_method'] ?? 'cash',
                'payment_date'   => $violation['fine_paid_date'] ?? date('Y-m-d'),
                'description'    => 'غرامة مخالفة — إدخال بأثر رجعي',
            ]);
        }

        $fineId = $db->insert('fines', [
            'member_id'      => $memberId,
            'violation_id'   => $violationId,
            'fine_type'      => 'violation_fine',
            'amount'         => $violation['fine_amount'] ?? '0.00',
            'penalty_type'   => $violation['penalty_type'] ?? 'fine',
            'suspension_from'=> self::emptyToNull($violation['suspension_from'] ?? null),
            'suspension_to'  => self::emptyToNull($violation['suspension_to'] ?? null),
            'status'         => $fineStatus,
            'paid_amount'    => $fineStatus === 'paid' ? ($violation['fine_amount'] ?? '0.00') : '0.00',
            'payment_id'     => $finePaymentId,
            'paid_at'        => $fineStatus === 'paid' ? (self::emptyToNull($violation['fine_paid_date'] ?? null) ?? date('Y-m-d H:i:s')) : null,
            'created_at'     => $violationDate . ' 09:00:00',
            'updated_at'     => date('Y-m-d H:i:s'),
        ]);

        return ['violation_id' => $violationId, 'fine_id' => $fineId, 'status' => $fineStatus];
    }

    private static function processRetroactiveRenewal(int $memberId, array $renewal): void
    {
        $db = App::getInstance()->db();

        $financialYear = $renewal['financial_year'];
        $status = $renewal['status'] ?? 'paid';

        $existing = $db->selectOne(
            "SELECT id FROM subscriptions WHERE member_id = ? AND financial_year = ? AND person_type = 'member'",
            [$memberId, $financialYear]
        );

        if ($existing) {
            $updateData = ['status' => $status, 'updated_at' => date('Y-m-d H:i:s')];
            if ($status === 'paid') {
                $updateData['paid_amount'] = $renewal['amount'] ?? '0.00';
                $updateData['paid_at'] = $renewal['paid_date'] ?? date('Y-m-d H:i:s');
            }
            $db->update('subscriptions', $updateData, '`id` = ?', [(int) $existing['id']]);
        }
    }

    private static function generateRetroReceiptNumber(string $date): string
    {
        $db = App::getInstance()->db();
        $year = date('Y', strtotime($date));
        $prefix = 'REC-' . $year . '-';
        $last = $db->selectOne(
            "SELECT receipt_number FROM receipts WHERE receipt_number LIKE ? ORDER BY id DESC LIMIT 1",
            [$prefix . '%']
        );
        if ($last) {
            $parts = explode('-', $last['receipt_number']);
            $seq = (int) end($parts) + 1;
        } else {
            $seq = 1;
        }
        return $prefix . str_pad((string) $seq, 6, '0', STR_PAD_LEFT);
    }
}
