<?php
declare(strict_types=1);

namespace App\Modules\Waiver\Services;

use App\Core\App;
use App\Core\EventBus;
use App\Core\Logger;
use App\Modules\Archive\Services\ArchiveService;
use App\Modules\Subscriptions\Services\SubscriptionSyncService;

final class WaiverProcessor
{
    public static function execute(int $waiverId): array
    {
        $db = App::getInstance()->db();
        $employee = App::getInstance()->currentEmployee();

        $waiver = $db->selectOne("SELECT * FROM waiver_requests WHERE id = ?", [$waiverId]);
        if (!$waiver || !in_array($waiver['status'], ['approved', 'fee_paid'])) {
            return ['success' => false, 'error' => 'طلب التنازل غير صالح أو غير مُعتمد'];
        }

        if (!$waiver['target_member_id']) {
            return ['success' => false, 'error' => 'لم يتم تحديد العضو المستفيد'];
        }

        $sourceMember = $db->selectOne("SELECT * FROM members WHERE id = ?", [(int) $waiver['source_member_id']]);
        if (!$sourceMember) return ['success' => false, 'error' => 'العضو المصدر غير موجود'];

        $targetMember = $db->selectOne("SELECT * FROM members WHERE id = ?", [(int) $waiver['target_member_id']]);
        if (!$targetMember) return ['success' => false, 'error' => 'العضو المستفيد غير موجود'];

        // Validate no children over 21 on target member (must be temporary members instead)
        $overAgeChildren = $db->select(
            "SELECT id, full_name_ar, date_of_birth, TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) as age_years
             FROM children WHERE member_id = ? AND is_archived = 0 AND TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) > 21",
            [(int) $targetMember['id']]
        );
        if (!empty($overAgeChildren)) {
            $names = array_map(fn($c) => $c['full_name_ar'] . ' (' . $c['age_years'] . ' سنة)', $overAgeChildren);
            return ['success' => false, 'error' => 'يوجد أبناء تجاوزوا 21 سنة ويجب تسجيلهم كأعضاء مؤقتين: ' . implode('، ', $names)];
        }

        $db->beginTransaction();
        try {
            $snapshotId = ArchiveService::takeSnapshot('members', (int) $sourceMember['id'], 'waiver', 'تنازل — طلب #' . $waiverId);

            // Transfer ALL active data from source to target before archiving
            self::transferActiveData($db, (int) $sourceMember['id'], (int) $targetMember['id']);

            // Find the waiver_fee payment made for this transfer (to set as activated_by_payment_id)
            $waiverPayment = $db->selectOne(
                "SELECT id FROM payments WHERE member_id = ? AND payment_type = 'waiver_fee' AND related_entity_type = 'waiver_requests' AND related_entity_id = ? AND is_voided = 0 ORDER BY id DESC LIMIT 1",
                [(int) $waiver['target_member_id'], $waiverId]
            );
            $waiverPaymentId = $waiverPayment ? (int) $waiverPayment['id'] : null;

            $db->update('members', [
                'membership_number' => null,
                'status'            => 'waived',
                'is_archived'       => 1,
                'archived_at'       => date('Y-m-d H:i:s'),
                'archived_by'       => $employee ? (int) $employee->id : null,
                'updated_at'        => date('Y-m-d H:i:s'),
            ], '`id` = ?', [(int) $waiver['source_member_id']]);

            $db->update('members', [
                'membership_number'          => $waiver['membership_number'],
                'status'                     => 'active',
                'membership_type'            => $sourceMember['membership_type'] ?? 'working',
                'membership_value'           => $sourceMember['membership_value'] ?? '0.00',
                'activated_by_payment_id'    => $waiverPaymentId,
                'activated_at'               => date('Y-m-d H:i:s'),
                'transferred_from_waiver_id' => $waiverId,
                'ownership_transfer_date'    => date('Y-m-d'),
                'membership_origin_date'     => $sourceMember['membership_origin_date'] ?? $sourceMember['form_date'] ?? substr($sourceMember['created_at'], 0, 10),
                'waived_from_member_name'    => $sourceMember['full_name_ar'] ?? null,
                'updated_at'                 => date('Y-m-d H:i:s'),
            ], '`id` = ?', [(int) $waiver['target_member_id']]);

            ArchiveService::recordNumberTransfer($waiver['membership_number'], 'waiver', 'members', (int) $waiver['target_member_id']);

            $db->update('waiver_requests', [
                'archive_snapshot_id' => $snapshotId,
                'status'              => 'completed',
                'updated_at'          => date('Y-m-d H:i:s'),
            ], '`id` = ?', [$waiverId]);

            $db->commit();

            // Generate subscription rows for target member and mark ALL family as paid
            $targetMemberId = (int) $waiver['target_member_id'];
            try {
                SubscriptionSyncService::syncForMember($targetMemberId);

                // Mark current year subscriptions as paid for ALL family members
                // (member + spouses + children + temporary — the waiver fee includes annual subscription)
                $now = new \DateTime();
                $month = (int) $now->format('n');
                $year = (int) $now->format('Y');
                $fyStart = $month >= 7 ? $year : $year - 1;
                $currentFy = $fyStart . '/' . ($fyStart + 1);

                $pendingSubs = $db->select(
                    "SELECT id, total_amount, fine_amount, development_fee FROM subscriptions WHERE member_id = ? AND financial_year = ? AND status IN ('pending','overdue')",
                    [$targetMemberId, $currentFy]
                );
                $ts = date('Y-m-d H:i:s');
                $paidByEmp = $employee ? (int) $employee->id : null;
                foreach ($pendingSubs as $sub) {
                    $paidAmount = bcadd(bcadd($sub['total_amount'], $sub['fine_amount'] ?? '0', 2), $sub['development_fee'] ?? '0', 2);
                    $db->update('subscriptions', [
                        'paid_amount' => $paidAmount,
                        'payment_id'  => $waiverPaymentId,
                        'status'      => 'paid',
                        'paid_at'     => $ts,
                        'paid_by'     => $paidByEmp,
                        'updated_at'  => $ts,
                    ], '`id` = ?', [(int) $sub['id']]);
                }

                // Also update dependent records to mark them as activated
                $db->query(
                    "UPDATE spouses SET activated_by_payment_id = ?, updated_at = ? WHERE member_id = ? AND is_archived = 0 AND activated_by_payment_id IS NULL",
                    [$waiverPaymentId, $ts, $targetMemberId]
                );
                $db->query(
                    "UPDATE children SET activated_by_payment_id = ?, updated_at = ? WHERE member_id = ? AND is_archived = 0 AND activated_by_payment_id IS NULL",
                    [$waiverPaymentId, $ts, $targetMemberId]
                );
                $db->query(
                    "UPDATE temporary_members SET activated_by_payment_id = ?, updated_at = ? WHERE member_id = ? AND is_archived = 0 AND activated_by_payment_id IS NULL",
                    [$waiverPaymentId, $ts, $targetMemberId]
                );
            } catch (\Throwable $e) {
                Logger::warning("Waiver subscription sync failed: " . $e->getMessage());
            }

            EventBus::dispatch('waiver.completed', [
                'waiver_id'        => $waiverId,
                'source_member_id' => (int) $waiver['source_member_id'],
                'target_member_id' => $targetMemberId,
                'membership_number'=> $waiver['membership_number'],
                'fee_amount'       => $waiver['waiver_fee_amount'] ?? '0.00',
                'fee_percentage'   => $waiver['waiver_fee_percentage'] ?? '30',
            ]);

            return ['success' => true, 'snapshot_id' => $snapshotId];
        } catch (\Throwable $e) {
            $db->rollBack();
            Logger::error("Waiver failed: " . $e->getMessage());
            return ['success' => false, 'error' => $e->getMessage()];
        }
    }

    /**
     * Auto-complete a waiver after fee payment — validates all conditions first.
     */
    public static function autoComplete(int $waiverId): array
    {
        $db = App::getInstance()->db();
        $waiver = $db->selectOne("SELECT * FROM waiver_requests WHERE id = ?", [$waiverId]);

        if (!$waiver || $waiver['status'] !== 'fee_paid') {
            return ['success' => false, 'reason' => 'status_invalid'];
        }

        if (!$waiver['target_member_id']) {
            return ['success' => false, 'reason' => 'no_target'];
        }

        if (!$waiver['approved_by']) {
            return ['success' => false, 'reason' => 'not_approved'];
        }

        $sourceDebtCheck = self::checkDebtsComprehensive((int) $waiver['source_member_id']);
        if (!$sourceDebtCheck['clear']) {
            return ['success' => false, 'reason' => 'source_debts', 'total' => $sourceDebtCheck['total']];
        }

        $targetDebtCheck = self::checkDebtsComprehensive((int) $waiver['target_member_id']);
        if (!$targetDebtCheck['clear']) {
            return ['success' => false, 'reason' => 'target_debts', 'total' => $targetDebtCheck['total']];
        }

        $originalDeps = [
            'spouses' => (int) ($waiver['original_spouses_count'] ?? 0),
            'children' => (int) ($waiver['original_children_count'] ?? 0),
            'temporary' => (int) ($waiver['original_temporary_count'] ?? 0),
            'total' => (int) ($waiver['original_dependent_count'] ?? 0),
        ];
        $targetDeps = self::countDependents((int) $waiver['target_member_id']);
        $comparison = self::compareDependents($originalDeps, $targetDeps);

        if ($comparison['has_excess']) {
            // Board must have reviewed: either individual fees saved OR legacy totals set
            $individualFees = self::getIndividualFees($waiverId);
            $totalApprovedExcessFee = bcadd(
                bcadd($waiver['spouse_fee_total'] ?? '0', $waiver['child_fee_total'] ?? '0', 2),
                $waiver['temporary_fee_total'] ?? '0', 2
            );
            $legacyExcessFee = $waiver['excess_fee_amount'] ?? '0';
            $boardReviewed = !empty($individualFees) || bccomp($totalApprovedExcessFee, '0', 2) > 0 || bccomp((string) $legacyExcessFee, '0', 2) > 0;
            if (!$boardReviewed) {
                return ['success' => false, 'reason' => 'excess_fees_not_set'];
            }
        }

        $db->update('waiver_requests', [
            'new_dependent_count'   => $targetDeps['total'],
            'new_spouses_count'     => $targetDeps['spouses'],
            'new_children_count'    => $targetDeps['children'],
            'new_temporary_count'   => $targetDeps['temporary'],
            'annual_renewal_paid'   => 1,
            'debts_cleared'         => 1,
            'target_debts_cleared'  => 1,
            'updated_at'            => date('Y-m-d H:i:s'),
        ], '`id` = ?', [$waiverId]);

        $result = self::execute($waiverId);
        if ($result['success']) {
            Logger::info("Waiver #{$waiverId} auto-completed after fee payment", [
                'source_member_id' => $waiver['source_member_id'],
                'target_member_id' => $waiver['target_member_id'],
            ]);
        }

        return $result;
    }

    /**
     * Comprehensive debt check: member + ALL dependents (spouses, children, temporary).
     * Checks ALL financial obligation types without exception.
     * Returns detailed breakdown per person with navigation links.
     */
    public static function checkDebtsComprehensive(int $memberId): array
    {
        $db = App::getInstance()->db();
        $member = $db->selectOne("SELECT id, full_name_ar, membership_number FROM members WHERE id = ?", [$memberId]);
        if (!$member) return ['clear' => true, 'debts' => [], 'total' => '0.00', 'details' => []];

        $memberName = $member['full_name_ar'] ?? 'عضو #' . $memberId;
        $details = [];

        // Build map of all dependents for labeling
        $dependentMap = self::buildDependentMap($memberId);

        // 1. Subscriptions (covers member + all dependents via person_type/person_id)
        $subs = $db->select(
            "SELECT id, person_type, person_id, person_name, financial_year, (total_amount - paid_amount + fine_amount + COALESCE(development_fee, 0)) as due
             FROM subscriptions WHERE member_id = ? AND status IN ('pending','overdue') AND (total_amount - paid_amount + fine_amount + COALESCE(development_fee, 0)) > 0",
            [$memberId]
        );
        foreach ($subs as $sub) {
            $personType = $sub['person_type'] ?? 'member';
            $personName = $sub['person_name'] ?? $memberName;
            $personLabel = self::getPersonLabel($personType, $personName);
            $details[] = [
                'member_id' => $memberId,
                'member_name' => $memberName,
                'person_name' => $personName,
                'person_type' => $personType,
                'person_label' => $personLabel,
                'debt_type' => 'اشتراك سنوي',
                'debt_type_code' => 'subscription',
                'period' => $sub['financial_year'] ?? '',
                'amount' => $sub['due'],
                'description' => 'اشتراك سنوي ' . ($sub['financial_year'] ?? '') . ' — ' . $personLabel,
                'payment_url' => '/payments/process/' . $memberId,
            ];
        }

        // 2. Fines (member-level — system stores all fines under member_id)
        $fines = $db->select(
            "SELECT id, notes, (amount - paid_amount) as due, created_at FROM fines WHERE member_id = ? AND status IN ('imposed','pending','appeal_upheld') AND (amount - paid_amount) > 0",
            [$memberId]
        );
        foreach ($fines as $fine) {
            $details[] = [
                'member_id' => $memberId,
                'member_name' => $memberName,
                'person_name' => $memberName,
                'person_type' => 'member',
                'person_label' => 'العضو الأساسي (' . $memberName . ')',
                'debt_type' => 'غرامة',
                'debt_type_code' => 'fine',
                'period' => substr($fine['created_at'] ?? '', 0, 10),
                'amount' => $fine['due'],
                'description' => 'غرامة' . (!empty($fine['notes']) ? ' — ' . $fine['notes'] : '') . ' — العضو الأساسي',
                'payment_url' => '/payments/process/' . $memberId,
            ];
        }

        // 3. Installments (active/overdue installment plans)
        $installments = $db->select(
            "SELECT s.id, s.due_date, (s.amount - s.paid_amount) as due, p.notes as plan_desc
             FROM installment_schedule s JOIN installment_plans p ON p.id = s.installment_plan_id
             WHERE p.member_id = ? AND p.status IN ('active','overdue') AND s.status IN ('pending','overdue') AND (s.amount - s.paid_amount) > 0",
            [$memberId]
        );
        foreach ($installments as $inst) {
            $details[] = [
                'member_id' => $memberId,
                'member_name' => $memberName,
                'person_name' => $memberName,
                'person_type' => 'member',
                'person_label' => 'العضو الأساسي (' . $memberName . ')',
                'debt_type' => 'قسط',
                'debt_type_code' => 'installment',
                'period' => $inst['due_date'] ?? '',
                'amount' => $inst['due'],
                'description' => 'قسط مستحق ' . ($inst['due_date'] ?? '') . (!empty($inst['plan_desc']) ? ' — ' . $inst['plan_desc'] : ''),
                'payment_url' => '/payments/process/' . $memberId,
            ];
        }

        // 4. Pending payment requests (includes service fees, admin fees, transfer fees, etc.)
        $requests = $db->select(
            "SELECT id, payment_type, amount, description_ar FROM payment_requests
             WHERE member_id = ? AND status = 'pending' AND is_voided = 0",
            [$memberId]
        );
        foreach ($requests as $req) {
            $typeLabel = self::getPaymentTypeLabel($req['payment_type'] ?? '');
            $details[] = [
                'member_id' => $memberId,
                'member_name' => $memberName,
                'person_name' => $memberName,
                'person_type' => 'member',
                'person_label' => 'العضو الأساسي (' . $memberName . ')',
                'debt_type' => $typeLabel,
                'debt_type_code' => 'payment_request',
                'period' => '',
                'amount' => $req['amount'],
                'description' => ($req['description_ar'] ?? $typeLabel),
                'payment_url' => '/payments/process/' . $memberId,
            ];
        }

        // 5. Unpaid sales (credit sales at POS/restaurant/etc.)
        $sales = $db->select(
            "SELECT id, invoice_number, (total_amount - amount_paid) as due, created_at
             FROM sales WHERE member_id = ? AND status = 'completed' AND (total_amount - amount_paid) > 0",
            [$memberId]
        );
        foreach ($sales as $sale) {
            $details[] = [
                'member_id' => $memberId,
                'member_name' => $memberName,
                'person_name' => $memberName,
                'person_type' => 'member',
                'person_label' => 'العضو الأساسي (' . $memberName . ')',
                'debt_type' => 'فاتورة مبيعات',
                'debt_type_code' => 'sale',
                'period' => substr($sale['created_at'] ?? '', 0, 10),
                'amount' => $sale['due'],
                'description' => 'فاتورة #' . ($sale['invoice_number'] ?? $sale['id']),
                'payment_url' => '/payments/process/' . $memberId,
            ];
        }

        // 6. Seasonal memberships with outstanding fees (if fee tracking exists)
        $seasonals = $db->select(
            "SELECT id, person_type, person_name, fee_amount, status
             FROM seasonal_memberships WHERE member_id = ? AND status IN ('pending','overdue') AND fee_amount > 0",
            [$memberId]
        );
        foreach ($seasonals as $sm) {
            $pType = $sm['person_type'] ?? 'member';
            $pName = $sm['person_name'] ?? $memberName;
            $details[] = [
                'member_id' => $memberId,
                'member_name' => $memberName,
                'person_name' => $pName,
                'person_type' => $pType,
                'person_label' => self::getPersonLabel($pType, $pName),
                'debt_type' => 'رسوم عضوية موسمية',
                'debt_type_code' => 'seasonal',
                'period' => '',
                'amount' => $sm['fee_amount'],
                'description' => 'عضوية موسمية — ' . self::getPersonLabel($pType, $pName),
                'payment_url' => '/payments/process/' . $memberId,
            ];
        }

        $totalDebt = '0.00';
        foreach ($details as $d) {
            $totalDebt = bcadd($totalDebt, (string) $d['amount'], 2);
        }

        return [
            'clear' => empty($details),
            'debts' => $details,
            'total' => $totalDebt,
            'member_name' => $memberName,
            'dependents_checked' => $dependentMap,
        ];
    }

    /**
     * Build a map of all dependents for a member (used to verify completeness).
     */
    private static function buildDependentMap(int $memberId): array
    {
        $db = App::getInstance()->db();
        $map = ['spouses' => [], 'children' => [], 'temporary' => []];

        $spouses = $db->select(
            "SELECT id, full_name_ar FROM spouses WHERE member_id = ? AND is_archived = 0",
            [$memberId]
        );
        foreach ($spouses as $s) {
            $map['spouses'][] = ['id' => (int) $s['id'], 'name' => $s['full_name_ar'] ?? ''];
        }

        $children = $db->select(
            "SELECT id, full_name_ar FROM children WHERE member_id = ? AND is_archived = 0",
            [$memberId]
        );
        foreach ($children as $c) {
            $map['children'][] = ['id' => (int) $c['id'], 'name' => $c['full_name_ar'] ?? ''];
        }

        $temps = $db->select(
            "SELECT id, full_name_ar FROM temporary_members WHERE member_id = ? AND is_archived = 0",
            [$memberId]
        );
        foreach ($temps as $t) {
            $map['temporary'][] = ['id' => (int) $t['id'], 'name' => $t['full_name_ar'] ?? ''];
        }

        return $map;
    }

    private static function getPaymentTypeLabel(string $type): string
    {
        return match ($type) {
            'subscription' => 'اشتراك سنوي',
            'fine' => 'غرامة',
            'installment' => 'قسط',
            'form_fee' => 'رسوم استمارة',
            'membership_fee' => 'رسوم عضوية',
            'down_payment' => 'مقدم عضوية',
            'waiver_fee' => 'رسوم تنازل',
            'transfer_fee' => 'رسوم تحويل',
            'separation_fee' => 'رسوم فصل',
            'service_fee' => 'رسوم خدمة',
            'admin_fee' => 'رسوم إدارية',
            'renewal_fee' => 'رسوم تجديد',
            'carnet_fee' => 'رسوم كارنيه',
            'guest_fee' => 'رسوم ضيافة',
            default => 'طلب دفع معلق',
        };
    }

    /**
     * Simple debt check (for backward compat and quick validation).
     */
    public static function checkDebts(int $memberId): array
    {
        $result = self::checkDebtsComprehensive($memberId);
        // Group by type for legacy format
        $grouped = [];
        foreach ($result['debts'] as $d) {
            $key = $d['debt_type'];
            if (!isset($grouped[$key])) {
                $grouped[$key] = ['type' => $key, 'count' => 0, 'amount' => '0.00'];
            }
            $grouped[$key]['count']++;
            $grouped[$key]['amount'] = bcadd($grouped[$key]['amount'], (string) $d['amount'], 2);
        }
        return [
            'clear' => $result['clear'],
            'debts' => array_values($grouped),
            'total' => $result['total'],
        ];
    }

    /**
     * Count dependents for a member (spouses + children + temporary) — per-category.
     */
    public static function countDependents(int $memberId): array
    {
        $db = App::getInstance()->db();
        $spouses = (int) ($db->selectOne("SELECT COUNT(*) as cnt FROM spouses WHERE member_id = ? AND is_archived = 0", [$memberId])['cnt'] ?? 0);
        $children = (int) ($db->selectOne("SELECT COUNT(*) as cnt FROM children WHERE member_id = ? AND is_archived = 0", [$memberId])['cnt'] ?? 0);
        $temps = (int) ($db->selectOne("SELECT COUNT(*) as cnt FROM temporary_members WHERE member_id = ? AND is_archived = 0", [$memberId])['cnt'] ?? 0);

        return [
            'spouses' => $spouses,
            'children' => $children,
            'temporary' => $temps,
            'total' => $spouses + $children + $temps,
        ];
    }

    /**
     * Get detailed dependent data for display (names, ages, DOB, categories).
     */
    public static function getDependentDetails(int $memberId): array
    {
        $db = App::getInstance()->db();

        $spouses = $db->select(
            "SELECT id, full_name_ar, national_id, status FROM spouses WHERE member_id = ? AND is_archived = 0 ORDER BY id",
            [$memberId]
        );

        $children = $db->select(
            "SELECT id, full_name_ar, national_id, date_of_birth, gender, classification, status
             FROM children WHERE member_id = ? AND is_archived = 0 ORDER BY date_of_birth",
            [$memberId]
        );

        // Calculate age and category for each child
        $today = new \DateTime();
        foreach ($children as &$child) {
            $dob = !empty($child['date_of_birth']) ? new \DateTime($child['date_of_birth']) : null;
            if ($dob) {
                $age = $dob->diff($today);
                $child['age_years'] = $age->y;
                $child['age_months'] = $age->m;
                $child['age_display'] = $age->y . ' سنة' . ($age->m > 0 ? ' و ' . $age->m . ' شهر' : '');
                if ($age->y < 12) {
                    $child['age_category'] = 'أقل من 12 سنة';
                    $child['age_category_code'] = 'under_12';
                } elseif ($age->y < 16) {
                    $child['age_category'] = 'من 12 إلى أقل من 16 سنة';
                    $child['age_category_code'] = '12_to_16';
                } elseif ($age->y < 18) {
                    $child['age_category'] = 'من 16 إلى أقل من 18 سنة';
                    $child['age_category_code'] = '16_to_18';
                } else {
                    $child['age_category'] = '18 سنة فأكثر';
                    $child['age_category_code'] = '18_plus';
                }
            } else {
                $child['age_years'] = null;
                $child['age_months'] = null;
                $child['age_display'] = '—';
                $child['age_category'] = 'غير محدد';
                $child['age_category_code'] = 'unknown';
            }
        }
        unset($child);

        $temporary = $db->select(
            "SELECT id, full_name_ar, national_id, status FROM temporary_members WHERE member_id = ? AND is_archived = 0 ORDER BY id",
            [$memberId]
        );

        return [
            'spouses' => $spouses,
            'children' => $children,
            'temporary' => $temporary,
        ];
    }

    /**
     * Compare dependents per-category: source (original) vs target (new).
     */
    public static function compareDependents(array $original, array $target): array
    {
        $excessSpouses = max(0, $target['spouses'] - $original['spouses']);
        $excessChildren = max(0, $target['children'] - $original['children']);
        $excessTemporary = max(0, $target['temporary'] - $original['temporary']);

        return [
            'excess_spouses' => $excessSpouses,
            'excess_children' => $excessChildren,
            'excess_temporary' => $excessTemporary,
            'excess_total' => $excessSpouses + $excessChildren + $excessTemporary,
            'has_excess' => ($excessSpouses + $excessChildren + $excessTemporary) > 0,
        ];
    }

    /**
     * Calculate per-category excess fees based on board-set rates.
     */
    public static function calculateExcessFees(array $excess, array $feeConfig, string $membershipValue): array
    {
        $spouseFee = '0.00';
        $childFee = '0.00';
        $tempFee = '0.00';

        if ($excess['excess_spouses'] > 0 && !empty($feeConfig['spouse_fee_type'])) {
            $spouseFee = self::calcCategoryFee(
                $excess['excess_spouses'],
                $feeConfig['spouse_fee_type'],
                $feeConfig['spouse_fee_rate'] ?? '0',
                $membershipValue
            );
        }

        if ($excess['excess_children'] > 0 && !empty($feeConfig['child_fee_type'])) {
            $childFee = self::calcCategoryFee(
                $excess['excess_children'],
                $feeConfig['child_fee_type'],
                $feeConfig['child_fee_rate'] ?? '0',
                $membershipValue
            );
        }

        if ($excess['excess_temporary'] > 0 && !empty($feeConfig['temporary_fee_type'])) {
            $tempFee = self::calcCategoryFee(
                $excess['excess_temporary'],
                $feeConfig['temporary_fee_type'],
                $feeConfig['temporary_fee_rate'] ?? '0',
                $membershipValue
            );
        }

        $total = bcadd(bcadd($spouseFee, $childFee, 2), $tempFee, 2);

        return [
            'spouse_fee_total' => $spouseFee,
            'child_fee_total' => $childFee,
            'temporary_fee_total' => $tempFee,
            'total_excess_fee' => $total,
        ];
    }

    private static function calcCategoryFee(int $excessCount, string $type, string $rate, string $membershipValue): string
    {
        if ($type === 'percentage') {
            $perUnit = bcdiv(bcmul($membershipValue, $rate, 4), '100', 2);
        } else {
            $perUnit = $rate;
        }
        return bcmul($perUnit, (string) $excessCount, 2);
    }

    /**
     * Get ALL dependents of the target member for board fee assignment.
     * Returns every spouse, child, and temporary member with full details.
     * The board decides independently which individuals to charge and how much.
     */
    public static function getAllTargetDependents(int $targetMemberId, array $originalCounts): array
    {
        $db = App::getInstance()->db();
        $today = new \DateTime();
        $all = [];

        $spouses = $db->select(
            "SELECT id, full_name_ar, national_id, status FROM spouses WHERE member_id = ? AND is_archived = 0 ORDER BY id",
            [$targetMemberId]
        );
        $allowedSpouses = $originalCounts['spouses'] ?? 0;
        $order = 1;
        foreach ($spouses as $idx => $s) {
            $all[] = [
                'person_type' => 'spouse',
                'person_id' => (int) $s['id'],
                'person_name' => $s['full_name_ar'] ?? '',
                'person_order' => $order++,
                'national_id' => $s['national_id'] ?? '',
                'status' => $s['status'] ?? 'active',
                'is_within_allowance' => ($idx < $allowedSpouses),
                'age_years' => null,
                'age_category' => null,
                'age_category_code' => null,
                'date_of_birth' => null,
                'relationship' => null,
            ];
        }

        $children = $db->select(
            "SELECT id, full_name_ar, national_id, date_of_birth, gender, relationship, classification, status
             FROM children WHERE member_id = ? AND is_archived = 0 ORDER BY date_of_birth",
            [$targetMemberId]
        );
        $allowedChildren = $originalCounts['children'] ?? 0;
        $order = 1;
        foreach ($children as $idx => $c) {
            $dob = !empty($c['date_of_birth']) ? new \DateTime($c['date_of_birth']) : null;
            $ageYears = $dob ? $dob->diff($today)->y : null;
            $ageCat = null;
            $ageCatCode = null;
            if ($ageYears !== null) {
                if ($ageYears < 12) { $ageCat = 'أقل من 12 سنة'; $ageCatCode = 'under_12'; }
                elseif ($ageYears < 16) { $ageCat = 'من 12 إلى أقل من 16 سنة'; $ageCatCode = '12_to_16'; }
                elseif ($ageYears < 18) { $ageCat = 'من 16 إلى أقل من 18 سنة'; $ageCatCode = '16_to_18'; }
                elseif ($ageYears < 25) { $ageCat = 'من 18 إلى أقل من 25 سنة'; $ageCatCode = '18_to_25'; }
                else { $ageCat = '25 سنة فأكثر'; $ageCatCode = '25_plus'; }
            }
            $all[] = [
                'person_type' => 'child',
                'person_id' => (int) $c['id'],
                'person_name' => $c['full_name_ar'] ?? '',
                'person_order' => $order++,
                'national_id' => $c['national_id'] ?? '',
                'status' => $c['status'] ?? 'active',
                'is_within_allowance' => ($idx < $allowedChildren),
                'age_years' => $ageYears,
                'age_category' => $ageCat,
                'age_category_code' => $ageCatCode,
                'date_of_birth' => $c['date_of_birth'] ?? null,
                'relationship' => $c['relationship'] ?? null,
                'gender' => $c['gender'] ?? null,
                'classification' => $c['classification'] ?? null,
            ];
        }

        $temps = $db->select(
            "SELECT id, full_name_ar, national_id, status FROM temporary_members WHERE member_id = ? AND is_archived = 0 ORDER BY id",
            [$targetMemberId]
        );
        $allowedTemps = $originalCounts['temporary'] ?? 0;
        $order = 1;
        foreach ($temps as $idx => $t) {
            $all[] = [
                'person_type' => 'temporary',
                'person_id' => (int) $t['id'],
                'person_name' => $t['full_name_ar'] ?? '',
                'person_order' => $order++,
                'national_id' => $t['national_id'] ?? '',
                'status' => $t['status'] ?? 'active',
                'is_within_allowance' => ($idx < $allowedTemps),
                'age_years' => null,
                'age_category' => null,
                'age_category_code' => null,
                'date_of_birth' => null,
                'relationship' => null,
            ];
        }

        return $all;
    }

    /**
     * Identify excess dependents (individual persons) for per-person fee assignment.
     * Compares target dependents vs original counts — returns the EXTRA individuals.
     * @deprecated Use getAllTargetDependents() instead — board should see all and decide.
     */
    public static function getExcessIndividuals(int $targetMemberId, array $originalCounts): array
    {
        $db = App::getInstance()->db();
        $today = new \DateTime();
        $excess = [];

        // Spouses: if target has more than original, the LAST ones are considered excess
        $spouses = $db->select(
            "SELECT id, full_name_ar, national_id, status FROM spouses WHERE member_id = ? AND is_archived = 0 ORDER BY id",
            [$targetMemberId]
        );
        $allowedSpouses = $originalCounts['spouses'] ?? 0;
        if (count($spouses) > $allowedSpouses) {
            $excessSpouses = array_slice($spouses, $allowedSpouses);
            $order = 1;
            foreach ($excessSpouses as $s) {
                $excess[] = [
                    'person_type' => 'spouse',
                    'person_id' => (int) $s['id'],
                    'person_name' => $s['full_name_ar'] ?? '',
                    'person_order' => $order++,
                    'national_id' => $s['national_id'] ?? '',
                    'status' => $s['status'] ?? 'active',
                    'age_years' => null,
                    'age_category' => null,
                    'age_category_code' => null,
                    'date_of_birth' => null,
                    'relationship' => null,
                ];
            }
        }

        // Children: last N are excess
        $children = $db->select(
            "SELECT id, full_name_ar, national_id, date_of_birth, gender, relationship, classification, status
             FROM children WHERE member_id = ? AND is_archived = 0 ORDER BY date_of_birth",
            [$targetMemberId]
        );
        $allowedChildren = $originalCounts['children'] ?? 0;
        if (count($children) > $allowedChildren) {
            $excessChildren = array_slice($children, $allowedChildren);
            $order = 1;
            foreach ($excessChildren as $c) {
                $dob = !empty($c['date_of_birth']) ? new \DateTime($c['date_of_birth']) : null;
                $ageYears = $dob ? $dob->diff($today)->y : null;
                $ageCat = null;
                $ageCatCode = null;
                if ($ageYears !== null) {
                    if ($ageYears < 12) { $ageCat = 'أقل من 12 سنة'; $ageCatCode = 'under_12'; }
                    elseif ($ageYears < 16) { $ageCat = 'من 12 إلى أقل من 16 سنة'; $ageCatCode = '12_to_16'; }
                    elseif ($ageYears < 18) { $ageCat = 'من 16 إلى أقل من 18 سنة'; $ageCatCode = '16_to_18'; }
                    elseif ($ageYears < 25) { $ageCat = 'من 18 إلى أقل من 25 سنة'; $ageCatCode = '18_to_25'; }
                    else { $ageCat = '25 سنة فأكثر'; $ageCatCode = '25_plus'; }
                }
                $excess[] = [
                    'person_type' => 'child',
                    'person_id' => (int) $c['id'],
                    'person_name' => $c['full_name_ar'] ?? '',
                    'person_order' => $order++,
                    'national_id' => $c['national_id'] ?? '',
                    'status' => $c['status'] ?? 'active',
                    'age_years' => $ageYears,
                    'age_category' => $ageCat,
                    'age_category_code' => $ageCatCode,
                    'date_of_birth' => $c['date_of_birth'] ?? null,
                    'relationship' => $c['relationship'] ?? null,
                    'gender' => $c['gender'] ?? null,
                    'classification' => $c['classification'] ?? null,
                ];
            }
        }

        // Temporary: last N are excess
        $temps = $db->select(
            "SELECT id, full_name_ar, national_id, status FROM temporary_members WHERE member_id = ? AND is_archived = 0 ORDER BY id",
            [$targetMemberId]
        );
        $allowedTemps = $originalCounts['temporary'] ?? 0;
        if (count($temps) > $allowedTemps) {
            $excessTemps = array_slice($temps, $allowedTemps);
            $order = 1;
            foreach ($excessTemps as $t) {
                $excess[] = [
                    'person_type' => 'temporary',
                    'person_id' => (int) $t['id'],
                    'person_name' => $t['full_name_ar'] ?? '',
                    'person_order' => $order++,
                    'national_id' => $t['national_id'] ?? '',
                    'status' => $t['status'] ?? 'active',
                    'age_years' => null,
                    'age_category' => null,
                    'age_category_code' => null,
                    'date_of_birth' => null,
                    'relationship' => null,
                ];
            }
        }

        return $excess;
    }

    /**
     * Save per-individual fees set by the board.
     */
    public static function saveIndividualFees(int $waiverId, array $fees, string $membershipValue): array
    {
        $db = App::getInstance()->db();

        // Clear old fees for this waiver
        $db->delete('waiver_individual_fees', '`waiver_request_id` = ?', [$waiverId]);

        $spouseTotal = '0.00';
        $childTotal = '0.00';
        $tempTotal = '0.00';

        foreach ($fees as $fee) {
            $feeType = $fee['fee_type'] ?? null;
            $feeRate = $fee['fee_rate'] ?? '0';
            $feeAmount = '0.00';

            if ($feeType === 'percentage' && is_numeric($feeRate)) {
                $feeAmount = bcdiv(bcmul($membershipValue, (string) $feeRate, 4), '100', 2);
            } elseif ($feeType === 'fixed' && is_numeric($feeRate)) {
                $feeAmount = number_format((float) $feeRate, 2, '.', '');
            }

            $dob = trim((string) ($fee['date_of_birth'] ?? ''));

            $db->insert('waiver_individual_fees', [
                'waiver_request_id' => $waiverId,
                'person_type' => $fee['person_type'],
                'person_id' => (int) $fee['person_id'],
                'person_name' => $fee['person_name'] ?? '',
                'person_order' => (int) ($fee['person_order'] ?? 1),
                'fee_type' => $feeType ?: null,
                'fee_rate' => is_numeric($feeRate) ? $feeRate : null,
                'fee_amount' => $feeAmount,
                'age_years' => $fee['age_years'] ?? null,
                'age_category' => $fee['age_category_code'] ?? $fee['age_category'] ?? null,
                'date_of_birth' => $dob !== '' ? $dob : null,
                'relationship' => ($fee['relationship'] ?? '') !== '' ? $fee['relationship'] : null,
                'status' => ($fee['status'] ?? '') !== '' ? $fee['status'] : null,
                'notes' => ($fee['notes'] ?? '') !== '' ? $fee['notes'] : null,
            ]);

            if ($fee['person_type'] === 'spouse') $spouseTotal = bcadd($spouseTotal, $feeAmount, 2);
            elseif ($fee['person_type'] === 'child') $childTotal = bcadd($childTotal, $feeAmount, 2);
            elseif ($fee['person_type'] === 'temporary') $tempTotal = bcadd($tempTotal, $feeAmount, 2);
        }

        $grandTotal = bcadd(bcadd($spouseTotal, $childTotal, 2), $tempTotal, 2);

        return [
            'spouse_total' => $spouseTotal,
            'child_total' => $childTotal,
            'temporary_total' => $tempTotal,
            'grand_total' => $grandTotal,
        ];
    }

    /**
     * Load saved per-individual fees for a waiver.
     */
    public static function getIndividualFees(int $waiverId): array
    {
        $db = App::getInstance()->db();
        return $db->select(
            "SELECT * FROM waiver_individual_fees WHERE waiver_request_id = ? ORDER BY person_type, person_order",
            [$waiverId]
        );
    }

    private static function getPersonLabel(?string $personType, string $personName): string
    {
        return match ($personType) {
            'member' => 'العضو الأساسي (' . $personName . ')',
            'spouse' => 'الزوجة (' . $personName . ')',
            'child' => 'الابن/ة (' . $personName . ')',
            'temporary' => 'العضو المؤقت (' . $personName . ')',
            default => $personName,
        };
    }

    /**
     * Transfer active data from source member to target member.
     * Called AFTER snapshot is taken so the archive preserves original state.
     * Payments and paid subscriptions stay with the archived source (historical record).
     * Only pending obligations and active dependents move to the new owner.
     */
    private static function transferActiveData($db, int $sourceId, int $targetId): void
    {
        $ts = date('Y-m-d H:i:s');

        // Dependents (only active/non-archived)
        $db->query(
            "UPDATE spouses SET member_id = ?, updated_at = ? WHERE member_id = ? AND is_archived = 0",
            [$targetId, $ts, $sourceId]
        );
        $db->query(
            "UPDATE children SET member_id = ?, updated_at = ? WHERE member_id = ? AND is_archived = 0",
            [$targetId, $ts, $sourceId]
        );
        $db->query(
            "UPDATE temporary_members SET member_id = ?, updated_at = ? WHERE member_id = ? AND is_archived = 0",
            [$targetId, $ts, $sourceId]
        );

        // Subscriptions: only pending/overdue move (paid/exempt are historical → stay with archived source)
        $db->query(
            "UPDATE subscriptions SET member_id = ? WHERE member_id = ? AND status IN ('pending','overdue')",
            [$targetId, $sourceId]
        );

        // Active installment plans move to new owner
        $db->query(
            "UPDATE installment_plans SET member_id = ? WHERE member_id = ? AND status = 'active'",
            [$targetId, $sourceId]
        );

        // Unpaid fines move to new owner
        $db->query(
            "UPDATE fines SET member_id = ? WHERE member_id = ? AND status IN ('imposed','appealed')",
            [$targetId, $sourceId]
        );

        // Documents (active ones)
        $db->query(
            "UPDATE documents SET member_id = ? WHERE member_id = ? AND is_archived = 0",
            [$targetId, $sourceId]
        );

        // Payment requests (pending ones move to new owner)
        $db->query(
            "UPDATE payment_requests SET member_id = ? WHERE member_id = ? AND is_voided = 0",
            [$targetId, $sourceId]
        );
    }
}
