<?php
declare(strict_types=1);

namespace App\Modules\Members\Services;

use App\Core\App;
use App\Core\EventBus;
use App\Core\Logger;
use App\Modules\Members\Services\NationalIdParser;

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');

        // Server-side mandatory field validation
        $errors = [];
        if (empty(trim($data['full_name_ar'] ?? ''))) $errors[] = 'الاسم بالعربية مطلوب';
        if (empty(trim($data['phone_mobile'] ?? ''))) $errors[] = 'رقم الهاتف المحمول مطلوب';
        if (empty(trim($data['join_date'] ?? ''))) $errors[] = 'تاريخ الانضمام مطلوب';
        if (empty(trim($data['form_date'] ?? ''))) $errors[] = 'تاريخ الاستمارة مطلوب';
        $hasNid = !empty(trim($data['national_id'] ?? '')) && strlen(trim($data['national_id'])) === 14;
        $hasDob = !empty(trim($data['date_of_birth'] ?? ''));
        if (!$hasNid && !$hasDob) $errors[] = 'تاريخ الميلاد مطلوب (أو أدخل الرقم القومي 14 رقم)';
        if (!empty($data['spouses'])) {
            foreach ($data['spouses'] as $si => $sp) {
                $n = $si + 1;
                if (empty(trim($sp['full_name_ar'] ?? ''))) $errors[] = "اسم الزوجة #{$n} مطلوب";
                if (empty(trim($sp['marriage_date'] ?? ''))) $errors[] = "تاريخ زواج الزوجة #{$n} مطلوب";
                $spNid = !empty(trim($sp['national_id'] ?? '')) && strlen(trim($sp['national_id'])) === 14;
                $spDob = !empty(trim($sp['date_of_birth'] ?? ''));
                if (!$spNid && !$spDob) $errors[] = "تاريخ ميلاد الزوجة #{$n} مطلوب (أو الرقم القومي)";
            }
        }
        if (!empty($data['children'])) {
            foreach ($data['children'] as $ci => $ch) {
                $n = $ci + 1;
                if (empty(trim($ch['full_name_ar'] ?? ''))) $errors[] = "اسم الابن/الابنة #{$n} مطلوب";
                $chNid = !empty(trim($ch['national_id'] ?? '')) && strlen(trim($ch['national_id'])) === 14;
                $chDob = !empty(trim($ch['date_of_birth'] ?? ''));
                if (!$chNid && !$chDob) $errors[] = "تاريخ ميلاد الابن/الابنة #{$n} مطلوب (أو الرقم القومي)";
            }
        }
        if (!empty($data['temporary_members'])) {
            foreach ($data['temporary_members'] as $ti => $tm) {
                $n = $ti + 1;
                if (empty(trim($tm['full_name_ar'] ?? ''))) $errors[] = "اسم العضو المؤقت #{$n} مطلوب";
                $tmNid = !empty(trim($tm['national_id'] ?? '')) && strlen(trim($tm['national_id'])) === 14;
                $tmDob = !empty(trim($tm['date_of_birth'] ?? ''));
                if (!$tmNid && !$tmDob) $errors[] = "تاريخ ميلاد العضو المؤقت #{$n} مطلوب (أو الرقم القومي)";
            }
        }
        if (!empty($errors)) {
            return ['success' => false, 'error' => implode("\n", $errors)];
        }

        $db->beginTransaction();
        try {
            $formNumber = (string) (MemberNumberGenerator::next() ?? 1);
            $joinDate = self::safeDate($data['join_date'] ?? null) ?? date('Y-m-d');
            $formDate = self::safeDate($data['form_date'] ?? null) ?? $joinDate;

            $nationalId = self::emptyToNull($data['national_id'] ?? null);
            $dateOfBirth = self::emptyToNull($data['date_of_birth'] ?? null);
            $gender = $data['gender'] ?? 'male';

            if ($nationalId && strlen($nationalId) === 14) {
                $nidParsed = NationalIdParser::parse($nationalId);
                if ($nidParsed['is_valid']) {
                    if (!$dateOfBirth && $nidParsed['dob']) {
                        $dateOfBirth = $nidParsed['dob'];
                    }
                    if ($nidParsed['gender']) {
                        $gender = $nidParsed['gender'];
                    }
                }
            }

            if (!$dateOfBirth) {
                return ['success' => false, 'error' => 'تاريخ الميلاد مطلوب — أدخل الرقم القومي (14 رقم) أو تاريخ الميلاد يدوياً'];
            }

            if ($nationalId) {
                $existing = $db->selectOne("SELECT id, full_name_ar FROM members WHERE national_id = ?", [$nationalId]);
                if ($existing) {
                    return ['success' => false, 'error' => 'الرقم القومي ' . $nationalId . ' مسجل بالفعل للعضو: ' . $existing['full_name_ar'] . ' (رقم ' . $existing['id'] . ')'];
                }
            }

            $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'      => $dateOfBirth,
                'gender'             => $gender ?: 'male',
                'phone_mobile'       => trim($data['phone_mobile'] ?? ''),
                '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' => self::safeTimestamp($formDate),
                'updated_at' => $ts,
                'created_by' => $empId,
            ]));

            if (!empty($data['membership_number'])) {
                $membershipNumber = $data['membership_number'];
                $db->update('members', [
                    'membership_number' => $membershipNumber,
                    'updated_at'        => $ts,
                ], '`id` = ?', [$memberId]);
            } else {
                $membershipNumber = MemberNumberGenerator::assign($memberId);
            }

            $db->update('members', [
                'activated_at'           => $joinDate . ' 09:00:00',
                'activated_by_payment_id' => 0,
            ], '`id` = ?', [$memberId]);

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

            // Form fee payment
            if (!empty($data['form_fee_amount']) && bccomp((string) ($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];
            }

            // Membership fee payment
            $membershipPaymentId = null;
            if (!empty($data['membership_value']) && bccomp((string) ($data['membership_value']), '0', 2) > 0) {
                $paymentDate = self::safeDate($data['membership_payment_date'] ?? null) ?? $joinDate;
                $paymentMethod = $data['membership_payment_method'] ?? 'cash';

                if ($paymentMethod === 'installment' && !empty($data['installment_plan'])) {
                    $downPayment = $data['installment_plan']['down_payment'] ?? '0.00';
                    $downPaymentDate = self::safeDate($data['installment_plan']['start_date'] ?? null) ?? $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];

                    $installmentResult = self::createRetroactiveInstallmentPlan($memberId, $data['installment_plan'], $membershipPaymentId);
                    $result['installments'] = $installmentResult;
                } else {
                    $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];
                }

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

            // Spouses
            if (!empty($data['spouses'])) {
                $spouseOrder = 0;
                foreach ($data['spouses'] as $spouse) {
                    $spouseOrder++;
                    $spouseJoinDate = self::safeDate($spouse['join_date'] ?? null) ?? $joinDate;
                    $spouseNid = self::emptyToNull($spouse['national_id'] ?? null);
                    $spouseDob = self::emptyToNull($spouse['date_of_birth'] ?? null);
                    $spouseGender = 'female';
                    if ($spouseNid && strlen($spouseNid) === 14) {
                        $parsed = NationalIdParser::parse($spouseNid);
                        if ($parsed['is_valid']) {
                            if (!$spouseDob && $parsed['dob']) $spouseDob = $parsed['dob'];
                            if ($parsed['gender']) $spouseGender = $parsed['gender'];
                        }
                    }
                    if (!$spouseDob) return ['success' => false, 'error' => 'تاريخ ميلاد الزوجة مطلوب'];

                    $spouseId = $db->insert('spouses', [
                        'member_id'       => $memberId,
                        'spouse_order'    => $spouseOrder,
                        'full_name_ar'    => $spouse['full_name_ar'],
                        'national_id'     => $spouseNid,
                        'date_of_birth'   => $spouseDob,
                        'gender'          => $spouseGender,
                        'nationality'     => self::emptyToNull($spouse['nationality'] ?? null) ?? 'مصري',
                        'marriage_date'   => self::safeDate($spouse['marriage_date'] ?? null) ?? $joinDate,
                        'join_date'       => $spouseJoinDate,
                        'classification'  => 'working',
                        'addition_fee'    => $spouse['addition_fee'] ?: '0.00',
                        'status'          => 'active',
                        'activated_by_payment_id' => $membershipPaymentId,
                        'is_archived'     => 0,
                        'created_at'      => self::safeTimestamp($spouseJoinDate),
                        'updated_at'      => $ts,
                    ]);

                    if (!empty($spouse['addition_fee']) && bccomp((string) ($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']];
                }
            }

            // Children
            if (!empty($data['children'])) {
                $childOrder = 0;
                foreach ($data['children'] as $child) {
                    $childOrder++;
                    $childJoinDate = self::safeDate($child['join_date'] ?? null) ?? $joinDate;
                    $childNid = self::emptyToNull($child['national_id'] ?? null);
                    $childDob = self::emptyToNull($child['date_of_birth'] ?? null);
                    $childGender = $child['gender'] ?? 'male';
                    if ($childNid && strlen($childNid) === 14) {
                        $parsed = NationalIdParser::parse($childNid);
                        if ($parsed['is_valid']) {
                            if (!$childDob && $parsed['dob']) $childDob = $parsed['dob'];
                            if ($parsed['gender']) $childGender = $parsed['gender'];
                        }
                    }
                    if (!$childDob) return ['success' => false, 'error' => 'تاريخ ميلاد الابن/الابنة مطلوب'];

                    $childId = $db->insert('children', [
                        'member_id'       => $memberId,
                        'child_order'     => $childOrder,
                        'full_name_ar'    => $child['full_name_ar'],
                        'national_id'     => $childNid,
                        'date_of_birth'   => $childDob,
                        'gender'          => $childGender ?: 'male',
                        'relationship'    => $childGender === 'female' ? 'daughter' : 'son',
                        'classification'  => 'included',
                        'addition_fee'    => $child['addition_fee'] ?: '0.00',
                        'status'          => 'active',
                        'activated_by_payment_id' => $membershipPaymentId,
                        'join_date'       => $childJoinDate,
                        'is_archived'     => 0,
                        'created_at'      => self::safeTimestamp($childJoinDate),
                        'updated_at'      => $ts,
                    ]);

                    if (!empty($child['addition_fee']) && bccomp((string) ($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']];
                }
            }

            // Temporary members
            if (!empty($data['temporary_members'])) {
                foreach ($data['temporary_members'] as $temp) {
                    $tempJoinDate = self::safeDate($temp['join_date'] ?? null) ?? $joinDate;
                    $tempNid = self::emptyToNull($temp['national_id'] ?? null);
                    $tempDob = self::emptyToNull($temp['date_of_birth'] ?? null);
                    $tempGender = $temp['gender'] ?? 'male';
                    if ($tempNid && strlen($tempNid) === 14) {
                        $parsed = NationalIdParser::parse($tempNid);
                        if ($parsed['is_valid']) {
                            if (!$tempDob && $parsed['dob']) $tempDob = $parsed['dob'];
                            if ($parsed['gender']) $tempGender = $parsed['gender'];
                        }
                    }
                    if (!$tempDob) return ['success' => false, 'error' => 'تاريخ ميلاد العضو المؤقت مطلوب'];

                    $tempId = $db->insert('temporary_members', [
                        'member_id'              => $memberId,
                        'full_name_ar'           => $temp['full_name_ar'],
                        'national_id'            => $tempNid,
                        'date_of_birth'          => $tempDob,
                        'gender'                 => $tempGender ?: 'male',
                        'category'               => $temp['category'] ?? 'parent',
                        'relationship_to_member' => self::emptyToNull($temp['relationship_to_member'] ?? null),
                        'status'                 => 'active',
                        'join_date'              => $tempJoinDate,
                        'addition_fee'           => $temp['addition_fee'] ?: '0.00',
                        'is_archived'            => 0,
                        'created_at'             => self::safeTimestamp($tempJoinDate),
                        'updated_at'             => $ts,
                    ]);

                    if (!empty($temp['addition_fee']) && bccomp((string) ($temp['addition_fee']), '0', 2) > 0 && !empty($temp['has_separate_fee'])) {
                        self::createRetroactivePayment($memberId, [
                            'payment_type'        => 'addition_fee',
                            'amount'              => $temp['addition_fee'],
                            'payment_method'      => $temp['payment_method'] ?? 'cash',
                            'payment_date'        => $temp['payment_date'] ?? $joinDate,
                            'related_entity_type' => 'temporary_members',
                            'related_entity_id'   => $tempId,
                            'description'         => 'رسوم إضافة عضو مؤقت — إدخال بأثر رجعي',
                        ]);
                    }

                    $result['dependents'][] = ['type' => 'temporary', 'id' => $tempId, 'name' => $temp['full_name_ar']];
                }
            }

            // Subscriptions
            $personIdMap = [
                'member'    => [$memberId],
                'spouse'    => [],
                'child'     => [],
                'temporary' => [],
            ];
            foreach ($result['dependents'] as $dep) {
                $personIdMap[$dep['type']][] = $dep['id'];
            }

            if (!empty($data['subscriptions'])) {
                foreach ($data['subscriptions'] as $sub) {
                    $personType = $sub['person_type'] ?? 'member';
                    $personIndex = (int) ($sub['person_index'] ?? 0);

                    if ($personType === 'member') {
                        $personId = $memberId;
                    } else {
                        // JS sends 1-based indices (child_1, child_2), convert to 0-based array index
                        $arrayIndex = max(0, $personIndex - 1);
                        if (isset($personIdMap[$personType][$arrayIndex])) {
                            $personId = $personIdMap[$personType][$arrayIndex];
                        } else {
                            $personId = self::resolveDependentId($db, $memberId, $personType, $sub['person_name'] ?? '');
                            if ($personId === null) {
                                continue;
                            }
                        }
                    }

                    $sub['person_id'] = $personId;

                    $subId = self::createRetroactiveSubscription($memberId, $sub);
                    $result['subscriptions'][] = $subId;
                }
            }

            // Violations & Fines
            if (!empty($data['violations'])) {
                foreach ($data['violations'] as $violation) {
                    $fineResult = self::createRetroactiveViolation($memberId, $violation);
                    $result['fines'][] = $fineResult;
                }
            }

            // Renewal history
            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()]);
            $userMessage = self::translateDbError($e->getMessage());
            return ['success' => false, 'error' => $userMessage];
        }
    }

    private static function translateDbError(string $message): string
    {
        if (str_contains($message, 'uq_members_national_id') || str_contains($message, 'Duplicate entry') && str_contains($message, 'national_id')) {
            preg_match("/Duplicate entry '([^']+)'/", $message, $m);
            $nid = $m[1] ?? '';
            return "الرقم القومي {$nid} مسجل بالفعل لعضو آخر — تأكد من الرقم أو ابحث عن العضو الموجود";
        }
        if (str_contains($message, 'Duplicate entry') && str_contains($message, 'phone_mobile')) {
            return 'رقم الهاتف مسجل بالفعل لعضو آخر';
        }
        if (str_contains($message, 'Duplicate entry')) {
            return 'بيانات مكررة — يوجد سجل بنفس البيانات بالفعل';
        }
        if (str_contains($message, 'foreign key constraint')) {
            return 'خطأ في الربط — تأكد أن جميع البيانات المرجعية صحيحة (الفرع، المؤهل، إلخ)';
        }
        if (str_contains($message, 'Data too long')) {
            return 'أحد الحقول يتجاوز الطول المسموح — تأكد من البيانات المدخلة';
        }
        return 'خطأ في حفظ البيانات: ' . mb_substr($message, 0, 200);
    }

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

    private static function safeDate(?string $value): ?string
    {
        if ($value === null || trim($value) === '') return null;
        $value = trim($value);
        if (preg_match('/^\d{4}-\d{2}-\d{2}$/', $value)) return $value;
        $t = strtotime($value);
        return $t ? date('Y-m-d', $t) : null;
    }

    private static function safeTimestamp(?string $date): string
    {
        $d = self::safeDate($date);
        return ($d ?? date('Y-m-d')) . ' 09:00:00';
    }

    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::safeDate($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'                  => (string) $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::safeDate($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'              => self::safeTimestamp($paymentDate),
            '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'                => (string) $data['amount'],
            'amount_in_words_ar'    => '',
            'description_ar'        => $data['description'] ?? 'دفعة بأثر رجعي',
            'issued_by_employee_id' => $empId,
            'issued_at'             => self::safeTimestamp($paymentDate),
            'is_voided'             => 0,
            'print_count'           => 0,
            'created_at'            => self::safeTimestamp($paymentDate),
        ]);

        $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 = (string) ($plan['total_amount'] ?? '0.00');
        $downPayment = (string) ($plan['down_payment'] ?? '0.00');
        $months = max(1, (int) ($plan['months'] ?? 12));
        $startDate = self::safeDate($plan['start_date'] ?? null) ?? date('Y-m-d');
        $interestRate = (string) ($plan['interest_rate'] ?? '22.00');

        $remaining = bcsub($totalAmount, $downPayment, 2);
        if (bccomp($remaining, '0', 2) <= 0) $remaining = '0.00';

        // Flat simple interest: remaining × (annual_rate/100) × (months/12)
        $totalInterest = bccomp($remaining, '0', 2) > 0
            ? bcmul(
                bcmul($remaining, bcdiv($interestRate, '100', 10), 10),
                bcdiv((string) $months, '12', 10),
                2
              )
            : '0.00';
        $totalWithInterest = bcadd($remaining, $totalInterest, 2);
        $avgMonthly = bccomp($remaining, '0', 2) > 0 ? bcdiv($totalWithInterest, (string) $months, 2) : '0.00';

        $monthlyPrincipal = bccomp($remaining, '0', 2) > 0 ? bcdiv($remaining, (string) $months, 2) : '0.00';
        $monthlyInterest  = bccomp($remaining, '0', 2) > 0 ? bcdiv($totalInterest, (string) $months, 2) : '0.00';

        $runningBalance = $remaining;
        $schedule = [];

        for ($i = 1; $i <= $months; $i++) {
            $isLast    = ($i === $months);
            $principal = $isLast ? $runningBalance : $monthlyPrincipal;
            $interest  = $isLast
                ? bcsub($totalWithInterest, bcadd($remaining, bcsub($totalInterest, $monthlyInterest, 2), 2), 2)
                : $monthlyInterest;
            if (bccomp($interest, '0', 2) < 0) $interest = '0.00';
            $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,
            ];
        }

        // down_payment_receipt is VARCHAR(50) in DB
        $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' => (string) $downPaymentId,
            'status'               => 'active',
            'created_at'           => self::safeTimestamp($startDate),
            'updated_at'           => date('Y-m-d H:i:s'),
            'created_by'           => $empId,
        ]);

        $paidInstallments = $plan['paid_installments'] ?? [];
        $paidCount = 0;

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

            foreach ($paidInstallments as $p) {
                if ((int) $p['number'] === $item['number']) {
                    $isPaid = true;
                    $paid = $p;
                    $paidAt = self::safeDate($p['paid_date'] ?? null) ?? $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 ? ($paidAt . ' 09:00:00') : null,
                'created_at'          => self::safeTimestamp($startDate),
                'updated_at'          => date('Y-m-d H:i:s'),
            ]);
        }

        if (!empty($plan['cheques'])) {
            foreach ($plan['cheques'] as $cheque) {
                if (empty($cheque['cheque_number'])) continue;
                $db->insert('installment_cheques', [
                    'installment_plan_id' => $planId,
                    'cheque_number'       => $cheque['cheque_number'],
                    'bank_name'           => $cheque['bank_name'] ?? 'غير محدد',
                    'cheque_date'         => self::safeDate($cheque['cheque_date'] ?? null) ?? date('Y-m-d'),
                    'cheque_amount'       => $cheque['cheque_amount'] ?? '0.00',
                    'scan_path'           => 'retroactive/no-scan.pdf',
                    'uploaded_by'         => $empId,
                    'notes'               => 'شيك بأثر رجعي — بدون صورة',
                    'created_at'          => date('Y-m-d H:i:s'),
                ]);
            }
        }

        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';
        $financialYear = $sub['financial_year'] ?? date('Y');
        $yearStart = explode('/', $financialYear)[0];
        if (!preg_match('/^\d{4}$/', $yearStart)) $yearStart = date('Y');

        $personType = $sub['person_type'] ?? 'member';
        $personId = (int) ($sub['person_id'] ?? 0);

        // Dedup guard: skip if subscription already exists for this combination
        $existing = $db->selectOne(
            "SELECT id FROM subscriptions WHERE member_id = ? AND financial_year = ? AND person_type = ? AND person_id = ?",
            [$memberId, $financialYear, $personType, $personId]
        );
        if ($existing) {
            return (int) $existing['id'];
        }

        $baseAmount = (string) ($sub['base_amount'] ?? '0.00');
        $devFee = ($personType === 'member') ? '35.00' : '0.00';

        // Apply year-specific discount from rules engine
        $discountAmount = '0.00';
        $yearAdjustment = \App\Modules\Rules\Services\RuleEngine::get('SUBSCRIPTION_YEAR_ADJUSTMENT_' . $yearStart);
        if ($yearAdjustment && isset($yearAdjustment['discount_percentage'])) {
            $discountAmount = bcdiv(bcmul($baseAmount, $yearAdjustment['discount_percentage'], 4), '100', 2);
        }

        // total_amount = base - discount (dev fee is separate, added at invoice level)
        $totalAmount = bcsub($baseAmount, $discountAmount, 2);

        // paid_amount only set when status is actually paid (includes dev fee on member row)
        $paidAmount = '0.00';
        if ($status === 'paid') {
            $paidAmount = bcadd($totalAmount, $devFee, 2);
        }

        $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'   => self::safeDate($sub['paid_date'] ?? null) ?? date('Y-m-d'),
                'description'    => "اشتراك سنوي {$financialYear} — إدخال بأثر رجعي",
            ]);
        }

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

        return $subId;
    }

    private static function resolveDependentId(\App\Core\Database $db, int $memberId, string $personType, string $personName): ?int
    {
        $tableMap = ['spouse' => 'spouses', 'child' => 'children', 'temporary' => 'temporary_members'];
        $table = $tableMap[$personType] ?? null;
        if (!$table) {
            return null;
        }

        $row = $db->selectOne(
            "SELECT id FROM `{$table}` WHERE member_id = ? AND full_name_ar = ? LIMIT 1",
            [$memberId, $personName]
        );

        return $row ? (int) $row['id'] : null;
    }

    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::safeDate($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'      => self::safeTimestamp($violationDate),
            'updated_at'      => date('Y-m-d H:i:s'),
        ]);

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

        if ($fineStatus === 'paid' && !empty($violation['fine_amount']) && bccomp((string) $violation['fine_amount'], '0', 2) > 0) {
            $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'         => (string) ($violation['fine_amount'] ?? '0.00'),
            'penalty_type'   => $violation['penalty_type'] ?? 'fine',
            'suspension_from'=> self::safeDate($violation['suspension_from'] ?? null),
            'suspension_to'  => self::safeDate($violation['suspension_to'] ?? null),
            'status'         => $fineStatus,
            'paid_amount'    => $fineStatus === 'paid' ? (string) ($violation['fine_amount'] ?? '0.00') : '0.00',
            'payment_id'     => $finePaymentId,
            'paid_at'        => $fineStatus === 'paid' ? ((self::safeDate($violation['fine_paid_date'] ?? null) ?? date('Y-m-d')) . ' 09:00:00') : null,
            'created_at'     => self::safeTimestamp($violationDate),
            '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'] ?? '';
        if (!$financialYear) return;
        $status = $renewal['status'] ?? 'paid';

        $existing = $db->selectOne(
            "SELECT id, total_amount 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') {
                $devFee = $db->selectOne(
                    "SELECT development_fee FROM subscriptions WHERE member_id = ? AND financial_year = ? AND development_fee > 0 LIMIT 1",
                    [$memberId, $financialYear]
                );
                $rowDevFee = (string) ($devFee['development_fee'] ?? '0.00');
                $baseAmount = (string) ($renewal['amount'] ?? $existing['total_amount']);
                $paidAmount = bcadd($baseAmount, $rowDevFee, 2);
                $paymentId = self::createRetroactivePayment($memberId, [
                    'payment_type'   => 'annual_subscription',
                    'amount'         => $paidAmount,
                    'payment_method' => $renewal['payment_method'] ?? 'cash',
                    'payment_date'   => self::safeDate($renewal['paid_date'] ?? null) ?? date('Y-m-d'),
                    'description'    => "اشتراك سنوي {$financialYear} — تجديد بأثر رجعي",
                ]);
                $updateData['paid_amount'] = $paidAmount;
                $updateData['payment_id'] = $paymentId;
                $updateData['paid_at'] = (self::safeDate($renewal['paid_date'] ?? null) ?? date('Y-m-d')) . ' 09:00:00';
            }
            $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);
    }
}
