<?php
declare(strict_types=1);

namespace App\Modules\Members\Services;

use App\Core\App;
use App\Modules\ServiceCatalog\Models\ServicePrice;
use App\Modules\Members\Services\BoardOfferService;
use App\Modules\Rules\Services\RuleEngine;

/**
 * Calculates the TOTAL bill for a member including all additions.
 * Used during initial creation to show accumulated charges before payment.
 */
final class BillingService
{
    public static function getMemberBill(int $memberId): array
    {
        $db = App::getInstance()->db();
        $member = $db->selectOne("SELECT * FROM members WHERE id = ? AND is_archived = 0", [$memberId]);
        if (!$member) return [];

        $membershipType = $member['membership_type'] ?? 'working';

        return match ($membershipType) {
            'honorary'  => self::getHonoraryBill($member, $memberId),
            'foreign'   => self::getForeignBill($member, $memberId),
            'seasonal'  => self::getSeasonalBill($member, $memberId),
            'sports'    => self::getSportsBill($member, $memberId),
            default     => self::getWorkingBill($member, $memberId),
        };
    }

    private static function getHonoraryBill(array $member, int $memberId): array
    {
        $db = App::getInstance()->db();
        $items = [];

        $formFeeData = self::getFormFeeItem($db, $memberId);
        $items[] = $formFeeData['item'];

        $items[] = [
            'type'        => 'membership_fee',
            'label'       => 'قيمة العضوية (عضوية شرفية — مجانية)',
            'amount'      => '0.00',
            'paid'        => true,
            'included'    => true,
            'included_note' => 'العضوية الشرفية بقرار مجلس الأمناء — بدون رسوم',
            'category'    => 'required',
        ];

        return self::buildBillResult($items, '0.00', $formFeeData['paid'], $formFeeData['pending']);
    }

    private static function getForeignBill(array $member, int $memberId): array
    {
        $db = App::getInstance()->db();
        $items = [];

        $formFeeData = self::getFormFeeItem($db, $memberId);
        $items[] = $formFeeData['item'];

        $branchCode = 'sheraton';
        if (!empty($member['branch_id'])) {
            $branch = $db->selectOne("SELECT branch_code FROM branches WHERE id = ?", [(int) $member['branch_id']]);
            $branchCode = $branch['branch_code'] ?? 'sheraton';
        }
        $feeRuleCode = $branchCode === 'new_capital' ? 'FOREIGN_MEMBER_FEE_CAPITAL' : 'FOREIGN_MEMBER_FEE_SHERATON';
        $feeData = RuleEngine::get($feeRuleCode);
        $feeUsd = $feeData['amount_usd'] ?? '10000.00';

        $foreignDetail = $db->selectOne(
            "SELECT fee_amount_usd, exchange_rate, fee_amount_egp FROM foreign_member_details WHERE member_id = ? AND is_archived = 0 ORDER BY id DESC LIMIT 1",
            [$memberId]
        );

        $displayAmount = $feeUsd;
        $breakdown = [];
        $breakdown[] = 'رسوم العضوية الأجنبية: $' . number_format((float) $feeUsd, 2) . ' USD';
        $breakdown[] = 'الفرع: ' . ($branchCode === 'new_capital' ? 'العاصمة الجديدة' : 'شيراتون');

        if ($foreignDetail) {
            if (!empty($foreignDetail['exchange_rate'])) {
                $egpAmount = $foreignDetail['fee_amount_egp'] ?? bcmul($feeUsd, $foreignDetail['exchange_rate'], 2);
                $breakdown[] = '═══════════════════════════';
                $breakdown[] = 'سعر الصرف: ' . number_format((float) $foreignDetail['exchange_rate'], 4) . ' ج.م/دولار';
                $breakdown[] = '💵 المبلغ بالجنيه: ' . number_format((float) $egpAmount, 2) . ' ج.م';
                $displayAmount = $egpAmount;
            }
        } else {
            $breakdown[] = '═══════════════════════════';
            $breakdown[] = 'سيتم تحديد سعر الصرف عند التسجيل';
        }

        $breakdown[] = '═══════════════════════════';
        $breakdown[] = 'شاملة الزوج/ة و 3 أبناء';

        $membershipPaid = false;
        $membershipPending = false;
        try {
            $mp = $db->selectOne(
                "SELECT id FROM payments WHERE member_id = ? AND payment_type IN ('membership_fee','down_payment','foreign_membership_fee') AND is_voided = 0 LIMIT 1",
                [$memberId]
            );
            $membershipPaid = ($mp !== null);
            if (!$membershipPaid) {
                $mpReq = $db->selectOne(
                    "SELECT id FROM payment_requests WHERE member_id = ? AND payment_type IN ('membership_fee','down_payment','foreign_membership_fee') AND is_voided = 0 AND status IN ('pending','processing') LIMIT 1",
                    [$memberId]
                );
                $membershipPending = ($mpReq !== null);
                if (!$membershipPending) {
                    $mpCompleted = $db->selectOne(
                        "SELECT id FROM payment_requests WHERE member_id = ? AND payment_type IN ('membership_fee','down_payment','foreign_membership_fee') AND is_voided = 0 AND status = 'completed' LIMIT 1",
                        [$memberId]
                    );
                    $membershipPaid = ($mpCompleted !== null);
                }
            }
        } catch (\Throwable $e) {}

        $items[] = [
            'type'        => 'membership_fee',
            'label'       => 'رسوم عضوية أجنبية ($' . number_format((float) $feeUsd) . ' USD)',
            'amount'      => $displayAmount,
            'paid'        => $membershipPaid,
            'in_queue'    => $membershipPending,
            'included'    => false,
            'category'    => 'required',
            'breakdown'   => $breakdown,
        ];

        return self::buildBillResult($items, $displayAmount, $formFeeData['paid'], $formFeeData['pending'], $membershipPaid, $membershipPending);
    }

    private static function getSeasonalBill(array $member, int $memberId): array
    {
        $db = App::getInstance()->db();
        $items = [];

        $formFeeData = self::getFormFeeItem($db, $memberId);
        $items[] = $formFeeData['item'];

        $seasonalRecord = $db->selectOne(
            "SELECT * FROM seasonal_memberships WHERE member_id = ? AND person_type = 'member' AND is_archived = 0 ORDER BY id DESC LIMIT 1",
            [$memberId]
        );

        $seasonalFee = $seasonalRecord['total_amount'] ?? '0.00';
        $seasonalPaid = ($seasonalRecord && ($seasonalRecord['status'] ?? '') === 'active');
        $seasonalPending = false;
        if (!$seasonalPaid) {
            $req = $db->selectOne(
                "SELECT id FROM payment_requests WHERE member_id = ? AND payment_type = 'seasonal_fee' AND is_voided = 0 AND status IN ('pending','processing') LIMIT 1",
                [$memberId]
            );
            $seasonalPending = ($req !== null);
            if (!$seasonalPending) {
                $completedReq = $db->selectOne(
                    "SELECT id FROM payment_requests WHERE member_id = ? AND payment_type = 'seasonal_fee' AND is_voided = 0 AND status = 'completed' LIMIT 1",
                    [$memberId]
                );
                $seasonalPaid = ($completedReq !== null);
            }
        }

        $durationLabel = $seasonalRecord ? ($seasonalRecord['duration_months'] ?? '—') . ' شهر' : '—';
        $nationalityLabel = match ($seasonalRecord['nationality_type'] ?? 'egyptian') {
            'foreign' => 'أجنبي',
            default   => 'مصري',
        };

        $breakdown = [];
        if ($seasonalRecord) {
            $breakdown[] = 'المدة: ' . ($seasonalRecord['duration_months'] ?? '—') . ' شهر';
            $breakdown[] = 'الجنسية: ' . $nationalityLabel;
            if (!empty($seasonalRecord['start_date']) && !empty($seasonalRecord['end_date'])) {
                $breakdown[] = 'من: ' . $seasonalRecord['start_date'] . ' إلى: ' . $seasonalRecord['end_date'];
            }
            $breakdown[] = '═══════════════════════════';
            $baseAmount = $seasonalRecord['fee_amount'] ?? '0.00';
            $breakdown[] = 'الرسوم الأساسية: ' . number_format((float) $baseAmount, 2) . ' ج.م';
            if (!empty($seasonalRecord['discount_amount']) && bccomp($seasonalRecord['discount_amount'], '0', 2) > 0) {
                $breakdown[] = 'خصم: -' . number_format((float) $seasonalRecord['discount_amount'], 2) . ' ج.م';
            }
            if (!empty($seasonalRecord['vat_amount']) && bccomp($seasonalRecord['vat_amount'], '0', 2) > 0) {
                $breakdown[] = 'ضريبة قيمة مضافة: ' . number_format((float) $seasonalRecord['vat_amount'], 2) . ' ج.م';
            }
            $breakdown[] = '💵 الإجمالي: ' . number_format((float) $seasonalFee, 2) . ' ج.م';

            $familyMembers = $db->select(
                "SELECT person_type, person_name, total_amount FROM seasonal_memberships WHERE member_id = ? AND person_type != 'member' AND is_archived = 0 AND id > ?",
                [$memberId, (int) $seasonalRecord['id']]
            );
            if (!empty($familyMembers)) {
                $breakdown[] = '═══════════════════════════';
                $breakdown[] = 'أفراد الأسرة المشمولين:';
                foreach ($familyMembers as $fm) {
                    $pType = match ($fm['person_type']) {
                        'spouse' => 'زوج/ة',
                        'child'  => 'ابن/ة',
                        default  => $fm['person_type'],
                    };
                    $breakdown[] = '  • ' . $pType . ': ' . $fm['person_name'] . ' — ' . number_format((float) $fm['total_amount'], 2) . ' ج.م';
                }
            }
        }

        $items[] = [
            'type'        => 'membership_fee',
            'label'       => 'رسوم عضوية موسمية (' . $durationLabel . ' — ' . $nationalityLabel . ')',
            'amount'      => $seasonalFee,
            'paid'        => $seasonalPaid,
            'in_queue'    => $seasonalPending,
            'included'    => false,
            'category'    => 'required',
            'breakdown'   => !empty($breakdown) ? $breakdown : null,
        ];

        $totalSeasonalFamily = '0.00';
        if ($seasonalRecord) {
            $familyRows = $db->select(
                "SELECT person_type, person_name, total_amount, status FROM seasonal_memberships WHERE member_id = ? AND person_type != 'member' AND is_archived = 0 ORDER BY id",
                [$memberId]
            );
            foreach ($familyRows as $fr) {
                $frPaid = ($fr['status'] === 'active');
                $frAmount = $fr['total_amount'] ?? '0.00';
                $totalSeasonalFamily = bcadd($totalSeasonalFamily, $frAmount, 2);
                $pLabel = match ($fr['person_type']) {
                    'spouse' => 'زوج/ة',
                    'child'  => 'ابن/ة',
                    default  => $fr['person_type'],
                };
                $items[] = [
                    'type'        => 'seasonal_family',
                    'label'       => 'عضوية موسمية — ' . $pLabel . ': ' . $fr['person_name'],
                    'amount'      => $frAmount,
                    'paid'        => $frPaid,
                    'included'    => false,
                    'category'    => 'addition',
                ];
            }
        }

        $totalFee = bcadd($seasonalFee, $totalSeasonalFamily, 2);
        return self::buildBillResult($items, $totalFee, $formFeeData['paid'], $formFeeData['pending'], $seasonalPaid, $seasonalPending);
    }

    private static function getSportsBill(array $member, int $memberId): array
    {
        $db = App::getInstance()->db();
        $items = [];

        $formFeeData = self::getFormFeeItem($db, $memberId);
        $items[] = $formFeeData['item'];

        $membershipValue = $member['membership_value'] ?? '0.00';
        if (bccomp($membershipValue, '0.01', 2) < 0 && !empty($member['qualification_id']) && !empty($member['branch_id'])) {
            $pricing = $db->selectOne(
                "SELECT price FROM pricing_configs WHERE branch_id = ? AND qualification_id = ? AND membership_type = 'working' AND is_active = 1 AND effective_from <= CURDATE() AND (effective_to IS NULL OR effective_to >= CURDATE()) ORDER BY effective_from DESC LIMIT 1",
                [(int) $member['branch_id'], (int) $member['qualification_id']]
            );
            if ($pricing && bccomp($pricing['price'], '0.01', 2) >= 0) {
                $membershipValue = $pricing['price'];
            }
        }

        $conversionRules = MembershipRulesService::getAthleticMemberConversionRules();
        $pct = $conversionRules['conversion_percentage'] ?? '50';
        $sportsFee = bcdiv(bcmul($membershipValue, $pct, 4), '100', 2);

        $membershipPaid = false;
        $membershipPending = false;
        try {
            $mp = $db->selectOne(
                "SELECT id FROM payments WHERE member_id = ? AND payment_type IN ('membership_fee','down_payment','sports_membership_fee') AND is_voided = 0 LIMIT 1",
                [$memberId]
            );
            $membershipPaid = ($mp !== null);
            if (!$membershipPaid) {
                $mpReq = $db->selectOne(
                    "SELECT id FROM payment_requests WHERE member_id = ? AND payment_type IN ('membership_fee','down_payment','sports_membership_fee') AND is_voided = 0 AND status IN ('pending','processing') LIMIT 1",
                    [$memberId]
                );
                $membershipPending = ($mpReq !== null);
                if (!$membershipPending) {
                    $mpCompleted = $db->selectOne(
                        "SELECT id FROM payment_requests WHERE member_id = ? AND payment_type IN ('membership_fee','down_payment','sports_membership_fee') AND is_voided = 0 AND status = 'completed' LIMIT 1",
                        [$memberId]
                    );
                    $membershipPaid = ($mpCompleted !== null);
                }
            }
        } catch (\Throwable $e) {}

        $items[] = [
            'type'        => 'membership_fee',
            'label'       => 'رسوم عضوية رياضية (' . $pct . '% من قيمة العضوية)',
            'amount'      => $sportsFee,
            'paid'        => $membershipPaid,
            'in_queue'    => $membershipPending,
            'included'    => false,
            'category'    => 'required',
            'breakdown'   => ['قيمة العضوية الأساسية: ' . number_format((float) $membershipValue, 2) . ' ج.م', 'نسبة التحويل: ' . $pct . '%', '═══════════════════════════', '💵 رسوم التحويل: ' . number_format((float) $sportsFee, 2) . ' ج.م'],
        ];

        return self::buildBillResult($items, $sportsFee, $formFeeData['paid'], $formFeeData['pending'], $membershipPaid, $membershipPending);
    }

    private static function getWorkingBill(array $member, int $memberId): array
    {
        $db = App::getInstance()->db();

        $membershipValue = $member['membership_value'] ?? '0.00';

        if (bccomp($membershipValue, '0.01', 2) < 0 && !empty($member['qualification_id']) && !empty($member['branch_id'])) {
            $pricing = $db->selectOne(
                "SELECT price FROM pricing_configs WHERE branch_id = ? AND qualification_id = ? AND membership_type = 'working' AND is_active = 1 AND effective_from <= CURDATE() AND (effective_to IS NULL OR effective_to >= CURDATE()) ORDER BY effective_from DESC LIMIT 1",
                [(int) $member['branch_id'], (int) $member['qualification_id']]
            );
            if ($pricing && bccomp($pricing['price'], '0.01', 2) >= 0) {
                $membershipValue = $pricing['price'];
            }
        }

        $items = [];

        // ── 1. Form Fee (505) ──
        $formFeePaid = false;
        $formFeePending = false;
        try {
            $ff = $db->selectOne(
                "SELECT id FROM payments WHERE member_id = ? AND payment_type = 'form_fee' AND is_voided = 0 LIMIT 1",
                [$memberId]
            );
            $formFeePaid = ($ff !== null);
            if (!$formFeePaid) {
                $ffReq = $db->selectOne(
                    "SELECT id, status FROM payment_requests WHERE member_id = ? AND payment_type = 'form_fee' AND is_voided = 0 AND status IN ('pending','processing') LIMIT 1",
                    [$memberId]
                );
                $formFeePending = ($ffReq !== null);
                if (!$formFeePending) {
                    $ffCompleted = $db->selectOne(
                        "SELECT id FROM payment_requests WHERE member_id = ? AND payment_type = 'form_fee' AND is_voided = 0 AND status = 'completed' LIMIT 1",
                        [$memberId]
                    );
                    $formFeePaid = ($ffCompleted !== null);
                }
            }
        } catch (\Throwable $e) {}

        $formFeeAmount = ServicePrice::getPrice('SVC_NEW_FORM', '505.00');

        $items[] = [
            'type'        => 'form_fee',
            'label'       => 'رسوم استمارة عضوية',
            'amount'      => $formFeeAmount,
            'paid'        => $formFeePaid,
            'included'    => false,
            'category'    => 'required',
            'breakdown'   => ['رسوم الاستمارة: 500.00 ج.م', 'طابع شهداء: 5.00 ج.م'],
        ];

        // ── 2. Membership Value ──
        $membershipPaid = false;
        $membershipPending = false;
        try {
            $mp = $db->selectOne(
                "SELECT id FROM payments WHERE member_id = ? AND payment_type IN ('membership_fee','down_payment') AND is_voided = 0 LIMIT 1",
                [$memberId]
            );
            $membershipPaid = ($mp !== null);
            if (!$membershipPaid) {
                $mpReq = $db->selectOne(
                    "SELECT id FROM payment_requests WHERE member_id = ? AND payment_type IN ('membership_fee','down_payment') AND is_voided = 0 AND status IN ('pending','processing') LIMIT 1",
                    [$memberId]
                );
                $membershipPending = ($mpReq !== null);
                if (!$membershipPending) {
                    $mpCompleted = $db->selectOne(
                        "SELECT id FROM payment_requests WHERE member_id = ? AND payment_type IN ('membership_fee','down_payment') AND is_voided = 0 AND status = 'completed' LIMIT 1",
                        [$memberId]
                    );
                    $membershipPaid = ($mpCompleted !== null);
                }
            }
        } catch (\Throwable $e) {}

        $qualName = '—';
        if ($member['qualification_id']) {
            $q = $db->selectOne("SELECT name_ar FROM qualifications WHERE id = ?", [(int) $member['qualification_id']]);
            $qualName = $q['name_ar'] ?? '—';
        }

        $items[] = [
            'type'        => 'membership_fee',
            'label'       => 'قيمة العضوية (' . $qualName . ')',
            'amount'      => $membershipValue,
            'paid'        => $membershipPaid,
            'included'    => false,
            'category'    => 'required',
        ];

        // ── 2b. Special Discount ──
        if (!empty($member['special_discount_id'])) {
            $discountRow = $db->selectOne(
                "SELECT sd.name_ar, sd.discount_percentage FROM special_discounts sd WHERE sd.id = ?",
                [(int) $member['special_discount_id']]
            );
            if ($discountRow) {
                $discountAmount = $member['discount_amount'] ?? bcdiv(bcmul($membershipValue, $discountRow['discount_percentage'], 4), '100', 2);
                // Discount is a negative line item
                $items[] = [
                    'type'        => 'special_discount',
                    'label'       => 'خصم خاص: ' . $discountRow['name_ar'] . ' (' . $discountRow['discount_percentage'] . '%)',
                    'amount'      => '-' . $discountAmount,
                    'paid'        => false,
                    'included'    => false,
                    'category'    => 'discount',
                ];
            }
        }

        // ── 3. Spouses ──
        $spouses = [];
        try {
            $spouses = $db->select(
                "SELECT * FROM spouses WHERE member_id = ? AND is_archived = 0 ORDER BY spouse_order",
                [$memberId]
            );
        } catch (\Throwable $e) {}

        $maxFreeSpouses = (int) (\App\Modules\Rules\Services\RuleEngine::requireValue('INITIAL_FREE_SPOUSES_COUNT', 'value'));
        foreach ($spouses as $s) {
            $order = (int) $s['spouse_order'];
            $fee = $s['addition_fee'] ?? '0.00';

            // Recalculate if fee is 0 for a non-free spouse and membership value is now known
            if ($order > $maxFreeSpouses && bccomp($fee, '0.01', 2) < 0 && bccomp($membershipValue, '0.01', 2) >= 0) {
                $ruleMap = [2 => 'SPOUSE_2ND_FEE', 3 => 'SPOUSE_3RD_FEE'];
                $ruleCode = $ruleMap[$order] ?? 'SPOUSE_4TH_FEE';
                try {
                    $ruleData = \App\Modules\Rules\Services\RuleEngine::require($ruleCode);
                    $pct = $ruleData['percentage'] ?? '10';
                    $fee = bcdiv(bcmul($membershipValue, $pct, 4), '100', 2);
                    $db->update('spouses', ['addition_fee' => $fee], '`id` = ?', [(int) $s['id']]);
                } catch (\Throwable $e) {}
            }

            $isFirstFree = ($order <= $maxFreeSpouses && bccomp($fee, '0', 2) <= 0);
            $isPaidViaStatus = ($s['status'] === 'active' && bccomp($fee, '0', 2) > 0);

            $breakdown = null;
            if (!empty($s['fee_breakdown_json'])) {
                $breakdown = json_decode($s['fee_breakdown_json'], true);
                if (!is_array($breakdown)) $breakdown = null;
            }

            $items[] = [
                'type'          => 'spouse_fee',
                'label'         => 'زوجة #' . $order . ' — ' . $s['full_name_ar'],
                'amount'        => $isFirstFree ? '0.00' : $fee,
                'paid'          => $isPaidViaStatus,
                'included'      => $isFirstFree,
                'included_note' => $isFirstFree ? 'مشمولة في قيمة العضوية' : null,
                'entity_type'   => 'spouses',
                'entity_id'     => (int) $s['id'],
                'category'      => 'addition',
                'breakdown'     => $breakdown,
            ];
        }

        // ── 4. Children ──
        $children = [];
        try {
            $children = $db->select(
                "SELECT * FROM children WHERE member_id = ? AND is_archived = 0 ORDER BY child_order",
                [$memberId]
            );
        } catch (\Throwable $e) {}

        foreach ($children as $c) {
            $order = (int) $c['child_order'];
            $fee = $c['addition_fee'] ?? '0.00';
            $classification = $c['classification'] ?? 'included';
            $isIncluded = ($classification === 'included' && bccomp($fee, '0', 2) <= 0);
            $isPaidViaStatus = (($c['status'] ?? '') === 'active' && bccomp($fee, '0', 2) > 0);

            $classLabel = match ($classification) {
                'included'           => 'مشمول (بدون رسوم)',
                'dependent_with_fee' => 'تابع (برسوم)',
                'temporary'          => 'مؤقت',
                default              => $classification,
            };

            $breakdown = null;
            if (!empty($c['fee_breakdown_json'])) {
                $breakdown = json_decode($c['fee_breakdown_json'], true);
                if (!is_array($breakdown)) $breakdown = null;
            }

            $items[] = [
                'type'          => 'child_fee',
                'label'         => ($c['gender'] === 'male' ? 'ابن' : 'ابنة') . ' #' . $order . ' — ' . $c['full_name_ar'] . ' (' . (int) ($c['age_years'] ?? 0) . ' سنة)',
                'amount'        => $fee,
                'paid'          => $isPaidViaStatus,
                'included'      => $isIncluded,
                'included_note' => $isIncluded ? 'مشمول — ' . $classLabel : $classLabel,
                'entity_type'   => 'children',
                'entity_id'     => (int) $c['id'],
                'category'      => 'addition',
                'breakdown'     => $breakdown,
            ];
        }

        // ── 5. Temporary Members ──
        try {
            $temps = $db->select(
                "SELECT * FROM temporary_members WHERE member_id = ? AND is_archived = 0 ORDER BY id",
                [$memberId]
            );
            foreach ($temps as $t) {
                $breakdown = null;
                if (!empty($t['fee_breakdown_json'])) {
                    $breakdown = json_decode($t['fee_breakdown_json'], true);
                    if (!is_array($breakdown)) $breakdown = null;
                }

                $tFee = $t['addition_fee'] ?? '0.00';
                $isPaidViaStatus = (($t['status'] ?? '') === 'active' && bccomp($tFee, '0', 2) > 0);

                $items[] = [
                    'type'        => 'temp_fee',
                    'label'       => 'عضو مؤقت — ' . $t['full_name_ar'] . ' (' . $t['category'] . ')',
                    'amount'      => $tFee,
                    'paid'        => $isPaidViaStatus,
                    'included'    => false,
                    'entity_type' => 'temporary_members',
                    'entity_id'   => (int) $t['id'],
                    'category'    => 'addition',
                    'breakdown'   => $breakdown,
                ];
            }
        } catch (\Throwable $e) {}

        // ── Check what's already paid ──
        try {
            $paidAdditions = $db->select(
                "SELECT related_entity_type, related_entity_id FROM payments
                 WHERE member_id = ? AND payment_type = 'addition_fee' AND is_voided = 0",
                [$memberId]
            );
            foreach ($paidAdditions as $pa) {
                foreach ($items as &$item) {
                    if (($item['entity_type'] ?? '') === $pa['related_entity_type']
                        && ($item['entity_id'] ?? 0) == $pa['related_entity_id']) {
                        $item['paid'] = true;
                    }
                }
                unset($item);
            }
        } catch (\Throwable $e) {}

        // ── Check payment_requests for completed or pending additions + load breakdowns ──
        try {
            $additionRequests = $db->select(
                "SELECT related_entity_type, related_entity_id, status, notes, payment_type FROM payment_requests
                 WHERE member_id = ? AND is_voided = 0",
                [$memberId]
            );
            foreach ($additionRequests as $ar) {
                foreach ($items as &$item) {
                    $matchesEntity = ($item['entity_type'] ?? '') === ($ar['related_entity_type'] ?? '')
                        && ($item['entity_id'] ?? 0) == ($ar['related_entity_id'] ?? 0)
                        && ($ar['payment_type'] === 'addition_fee');
                    $matchesType = !isset($item['entity_type'])
                        && (($item['type'] === 'form_fee' && $ar['payment_type'] === 'form_fee')
                         || ($item['type'] === 'membership_fee' && in_array($ar['payment_type'], ['membership_fee', 'down_payment'], true)));

                    if ($matchesEntity || $matchesType) {
                        if ($ar['status'] === 'completed') {
                            $item['paid'] = true;
                        } elseif (in_array($ar['status'], ['pending', 'processing'], true)) {
                            $item['in_queue'] = true;
                        }
                        if (!empty($ar['notes'])) {
                            $nd = json_decode($ar['notes'], true);
                            if (is_array($nd) && !empty($nd['fee_breakdown'])) {
                                $item['breakdown'] = $nd['fee_breakdown'];
                            }
                        }
                    }
                }
                unset($item);
            }
        } catch (\Throwable $e) {}

        // ── 6. Board Offers (cash discount) ──
        $cashOffer = BoardOfferService::getCashDiscount();
        if ($cashOffer && ($cashOffer['applies_to'] === 'membership_fee' || $cashOffer['applies_to'] === 'all')) {
            $items[] = [
                'type'        => 'board_offer',
                'label'       => 'عرض مجلس الإدارة: ' . $cashOffer['title_ar'] . ' (' . $cashOffer['discount_percentage'] . '%)',
                'amount'      => '0.00',
                'paid'        => false,
                'included'    => false,
                'category'    => 'offer',
                'offer'       => $cashOffer,
            ];
        }

        // ── Calculate totals ──
        $totalRequired = '0.00';
        $totalPaid = '0.00';
        $totalPending = '0.00';
        $totalInQueue = '0.00';
        $totalIncluded = '0.00';

        foreach ($items as $item) {
            if ($item['included']) {
                $totalIncluded = bcadd($totalIncluded, $item['amount'], 2);
                continue;
            }
            $totalRequired = bcadd($totalRequired, $item['amount'], 2);
            if ($item['paid']) {
                $totalPaid = bcadd($totalPaid, $item['amount'], 2);
            } elseif (!empty($item['in_queue'])) {
                $totalInQueue = bcadd($totalInQueue, $item['amount'], 2);
            } else {
                $totalPending = bcadd($totalPending, $item['amount'], 2);
            }
        }

        return [
            'items'              => $items,
            'membership_value'   => $membershipValue,
            'total_required'     => $totalRequired,
            'total_paid'         => $totalPaid,
            'total_pending'      => $totalPending,
            'total_in_queue'     => $totalInQueue,
            'total_included'     => $totalIncluded,
            'form_fee_paid'      => $formFeePaid,
            'form_fee_pending'   => $formFeePending ?? false,
            'membership_paid'    => $membershipPaid,
            'membership_pending' => $membershipPending ?? false,
            'all_paid'           => (bccomp($totalPending, '0', 2) <= 0 && bccomp($totalInQueue, '0', 2) <= 0),
        ];
    }

    // ══════════════════════════════════════════════════════════════════════
    // Shared Helpers
    // ══════════════════════════════════════════════════════════════════════

    private static function getFormFeeItem(\App\Core\Database $db, int $memberId): array
    {
        $formFeePaid = false;
        $formFeePending = false;
        try {
            $ff = $db->selectOne(
                "SELECT id FROM payments WHERE member_id = ? AND payment_type = 'form_fee' AND is_voided = 0 LIMIT 1",
                [$memberId]
            );
            $formFeePaid = ($ff !== null);
            if (!$formFeePaid) {
                $ffReq = $db->selectOne(
                    "SELECT id, status FROM payment_requests WHERE member_id = ? AND payment_type = 'form_fee' AND is_voided = 0 AND status IN ('pending','processing') LIMIT 1",
                    [$memberId]
                );
                $formFeePending = ($ffReq !== null);
                if (!$formFeePending) {
                    $ffCompleted = $db->selectOne(
                        "SELECT id FROM payment_requests WHERE member_id = ? AND payment_type = 'form_fee' AND is_voided = 0 AND status = 'completed' LIMIT 1",
                        [$memberId]
                    );
                    $formFeePaid = ($ffCompleted !== null);
                }
            }
        } catch (\Throwable $e) {}

        $formFeeAmount = ServicePrice::getPrice('SVC_NEW_FORM', '505.00');

        return [
            'paid' => $formFeePaid,
            'pending' => $formFeePending,
            'item' => [
                'type'        => 'form_fee',
                'label'       => 'رسوم استمارة عضوية',
                'amount'      => $formFeeAmount,
                'paid'        => $formFeePaid,
                'in_queue'    => $formFeePending,
                'included'    => false,
                'category'    => 'required',
                'breakdown'   => ['رسوم الاستمارة: 500.00 ج.م', 'طابع شهداء: 5.00 ج.م'],
            ],
        ];
    }

    private static function buildBillResult(
        array $items,
        string $membershipValue,
        bool $formFeePaid = false,
        bool $formFeePending = false,
        bool $membershipPaid = false,
        bool $membershipPending = false
    ): array {
        $totalRequired = '0.00';
        $totalPaid = '0.00';
        $totalPending = '0.00';
        $totalInQueue = '0.00';
        $totalIncluded = '0.00';

        foreach ($items as $item) {
            if ($item['included'] ?? false) {
                $totalIncluded = bcadd($totalIncluded, $item['amount'], 2);
                continue;
            }
            $totalRequired = bcadd($totalRequired, $item['amount'], 2);
            if ($item['paid'] ?? false) {
                $totalPaid = bcadd($totalPaid, $item['amount'], 2);
            } elseif (!empty($item['in_queue'])) {
                $totalInQueue = bcadd($totalInQueue, $item['amount'], 2);
            } else {
                $totalPending = bcadd($totalPending, $item['amount'], 2);
            }
        }

        return [
            'items'              => $items,
            'membership_value'   => $membershipValue,
            'total_required'     => $totalRequired,
            'total_paid'         => $totalPaid,
            'total_pending'      => $totalPending,
            'total_in_queue'     => $totalInQueue,
            'total_included'     => $totalIncluded,
            'form_fee_paid'      => $formFeePaid,
            'form_fee_pending'   => $formFeePending,
            'membership_paid'    => $membershipPaid,
            'membership_pending' => $membershipPending,
            'all_paid'           => (bccomp($totalPending, '0', 2) <= 0 && bccomp($totalInQueue, '0', 2) <= 0),
        ];
    }
}