<?php
declare(strict_types=1);

namespace App\Modules\Death\Controllers;

use App\Core\Controller;
use App\Core\Request;
use App\Core\Response;
use App\Core\App;
use App\Core\EventBus;
use App\Core\Logger;
use App\Modules\Death\Models\DeathCase;
use App\Modules\Archive\Services\ArchiveService;
use App\Modules\Rules\Services\RuleEngine;
use App\Modules\Payments\Services\PaymentService;
use App\Modules\Cashier\Services\PaymentRequestService;
use App\Modules\Members\Services\NationalIdParser;
use App\Modules\ServiceCatalog\Models\ServicePrice;
use App\Modules\Death\Services\DeathAuditService;

class DeathController extends Controller
{
    private static function getFees(): array
    {
        $formFee = ServicePrice::getPrice('SVC_TRANSFER_FORM', '570.00');
        $annualSubBase = ServicePrice::getPrice('SVC_ANNUAL_MEMBER', '492.00');
        $devFeeData = RuleEngine::get('DEVELOPMENT_FEE');
        $devFee = $devFeeData['amount'] ?? '35.00';
        $annualSub = bcadd($annualSubBase, $devFee, 2);
        $totalFee = bcadd($formFee, $annualSub, 2);
        return compact('formFee', 'annualSubBase', 'devFee', 'annualSub', 'totalFee');
    }

    /**
     * Calculate annual subscription for the NEW membership after death transfer.
     *
     * The deceased member is dead — we do NOT charge subscription for him.
     * The primary spouse BECOMES the new member (1 × member_sub).
     * Remaining spouses (minus primary, minus secondaries who get own memberships)
     * stay as spouse dependents of the new member.
     * Children and temps assigned to primary are counted.
     *
     * @param int $memberId          Deceased member's ID (to count current dependents)
     * @param int $primarySpouseId   The spouse who becomes new member (excluded from spouse count)
     * @param array $secondaryIds    Spouses getting separate memberships (excluded from spouse count)
     */
    private static function calculateFamilyAnnualSub(int $memberId, int $primarySpouseId = 0, array $secondaryIds = []): array
    {
        $db = App::getInstance()->db();
        $memberSub = ServicePrice::getPrice('SVC_ANNUAL_MEMBER', '492.00');
        $spouseSub = ServicePrice::getPrice('SVC_ANNUAL_SPOUSE', '492.00');
        $childSub  = ServicePrice::getPrice('SVC_ANNUAL_CHILD', '222.00');
        $tempSub   = ServicePrice::getPrice('SVC_ANNUAL_TEMP', '222.00');
        $devFeeData = RuleEngine::get('DEVELOPMENT_FEE');
        $devFee = $devFeeData['amount'] ?? '35.00';

        // If the deceased member already has current FY subscriptions, don't charge again
        $currentMonth = (int) date('n');
        $currentYear = (int) date('Y');
        $financialYear = $currentMonth >= 7
            ? $currentYear . '/' . ($currentYear + 1)
            : ($currentYear - 1) . '/' . $currentYear;

        $existingCurrentFY = $db->selectOne(
            "SELECT id FROM subscriptions WHERE member_id = ? AND financial_year = ? AND person_type = 'member' LIMIT 1",
            [$memberId, $financialYear]
        );
        if ($existingCurrentFY) {
            // Current year already covered — no annual sub needed
            return [
                'annual_sub_total' => '0.00',
                'member_sub'       => $memberSub,
                'spouse_sub'       => $spouseSub,
                'child_sub'        => $childSub,
                'temp_sub'         => $tempSub,
                'dev_fee'          => '0.00',
                'spouse_count'     => 0,
                'child_count'      => 0,
                'temp_count'       => 0,
                'current_fy_covered' => true,
            ];
        }

        // Count remaining spouses (exclude primary who becomes member, exclude secondaries who get own memberships)
        $excludeIds = array_merge([$primarySpouseId], array_map('intval', $secondaryIds));
        $excludeIds = array_filter($excludeIds);
        $spouseCount = 0;
        if (!empty($excludeIds)) {
            $placeholders = implode(',', array_fill(0, count($excludeIds), '?'));
            $params = array_merge([$memberId], $excludeIds);
            $spouseCount = (int) ($db->selectOne(
                "SELECT COUNT(*) as cnt FROM spouses WHERE member_id = ? AND is_archived = 0 AND status = 'active' AND id NOT IN ({$placeholders})",
                $params
            )['cnt'] ?? 0);
        } else {
            $spouseCount = (int) ($db->selectOne(
                "SELECT COUNT(*) as cnt FROM spouses WHERE member_id = ? AND is_archived = 0 AND status = 'active'",
                [$memberId]
            )['cnt'] ?? 0);
        }

        $childCount = (int) ($db->selectOne(
            "SELECT COUNT(*) as cnt FROM children WHERE member_id = ? AND is_archived = 0 AND status = 'active'",
            [$memberId]
        )['cnt'] ?? 0);
        $tempCount = (int) ($db->selectOne(
            "SELECT COUNT(*) as cnt FROM temporary_members WHERE member_id = ? AND is_archived = 0 AND status = 'active'",
            [$memberId]
        )['cnt'] ?? 0);

        // 1 × member (the primary spouse IS the new member) + remaining spouses + children + temps
        $familyTotal = $memberSub;
        $familyTotal = bcadd($familyTotal, bcmul($spouseSub, (string) $spouseCount, 2), 2);
        $familyTotal = bcadd($familyTotal, bcmul($childSub, (string) $childCount, 2), 2);
        $familyTotal = bcadd($familyTotal, bcmul($tempSub, (string) $tempCount, 2), 2);
        $annualSubTotal = bcadd($familyTotal, $devFee, 2);

        return [
            'annual_sub_total' => $annualSubTotal,
            'member_sub'       => $memberSub,
            'spouse_sub'       => $spouseSub,
            'child_sub'        => $childSub,
            'temp_sub'         => $tempSub,
            'dev_fee'          => $devFee,
            'spouse_count'     => $spouseCount,
            'child_count'      => $childCount,
            'temp_count'       => $tempCount,
            'current_fy_covered' => false,
        ];
    }

    private static function uploadDocument(array $fileInfo, string $prefix, int $memberId): array
    {
        $allowedMimes = ['application/pdf', 'image/jpeg', 'image/png', 'image/gif'];
        $allowedExts  = ['pdf', 'jpg', 'jpeg', 'png', 'gif'];

        if (empty($fileInfo['tmp_name']) || $fileInfo['error'] !== UPLOAD_ERR_OK) {
            $errMap = [
                UPLOAD_ERR_INI_SIZE   => 'حجم الملف أكبر من المسموح',
                UPLOAD_ERR_FORM_SIZE  => 'حجم الملف أكبر من المسموح',
                UPLOAD_ERR_PARTIAL    => 'رُفع الملف جزئياً',
                UPLOAD_ERR_NO_FILE    => 'لم يتم اختيار ملف',
                UPLOAD_ERR_NO_TMP_DIR => 'مجلد مؤقت غير موجود',
                UPLOAD_ERR_CANT_WRITE => 'فشل كتابة الملف',
                UPLOAD_ERR_EXTENSION  => 'نوع الملف غير مسموح',
            ];
            return ['success' => false, 'error' => $errMap[$fileInfo['error'] ?? UPLOAD_ERR_NO_FILE] ?? 'خطأ في رفع الملف'];
        }

        $finfo = new \finfo(FILEINFO_MIME_TYPE);
        $mime  = $finfo->file($fileInfo['tmp_name']);
        if (!in_array($mime, $allowedMimes, true)) {
            return ['success' => false, 'error' => 'نوع الملف غير مسموح — يجب أن يكون PDF أو صورة (JPG/PNG)'];
        }

        $ext = strtolower(pathinfo($fileInfo['name'], PATHINFO_EXTENSION));
        if (!in_array($ext, $allowedExts, true)) {
            return ['success' => false, 'error' => 'امتداد الملف غير مسموح'];
        }

        $uploadDir = App::getInstance()->basePath() . '/storage/uploads/death/';
        if (!is_dir($uploadDir)) {
            mkdir($uploadDir, 0755, true);
        }

        $filename = $prefix . '_' . $memberId . '_' . time() . '_' . bin2hex(random_bytes(2)) . '.' . $ext;
        $destPath = $uploadDir . $filename;

        if (!move_uploaded_file($fileInfo['tmp_name'], $destPath)) {
            return ['success' => false, 'error' => 'فشل حفظ الملف على الخادم'];
        }

        return ['success' => true, 'path' => 'storage/uploads/death/' . $filename];
    }

    public function index(Request $request): Response
    {
        $filters = ['search' => trim((string) $request->get('q', '')), 'status' => $request->get('status', '')];
        $page = max(1, (int) $request->get('page', 1));
        $result = DeathCase::search($filters, 25, $page);
        return $this->view('Death.Views.index', ['rows' => $result['data'], 'pagination' => $result['pagination'], 'filters' => $filters]);
    }

    public function create(Request $request, string $memberId): Response
    {
        $db = App::getInstance()->db();
        $member = $db->selectOne("SELECT * FROM members WHERE id = ? AND is_archived = 0", [(int) $memberId]);
        if (!$member) return $this->redirect('/members')->withError('العضو غير موجود');

        // Check for overdue/unpaid subscriptions from PREVIOUS years only
        // Current FY is exempt: member died before using the membership this year
        $currentMonth = (int) date('n');
        $currentYear = (int) date('Y');
        $currentFY = $currentMonth >= 7
            ? $currentYear . '/' . ($currentYear + 1)
            : ($currentYear - 1) . '/' . $currentYear;

        $overdueSubscriptions = $db->select(
            "SELECT financial_year, COUNT(*) as cnt, SUM(total_amount - paid_amount) as total_due
             FROM subscriptions
             WHERE member_id = ? AND status IN ('pending', 'overdue') AND financial_year != ?
             GROUP BY financial_year
             ORDER BY financial_year ASC",
            [(int) $memberId, $currentFY]
        );
        $totalOverdue = '0.00';
        foreach ($overdueSubscriptions as $row) {
            $totalOverdue = bcadd($totalOverdue, $row['total_due'] ?? '0', 2);
        }

        $spouses = $db->select("SELECT * FROM spouses WHERE member_id = ? AND is_archived = 0 AND status = 'active' ORDER BY spouse_order", [(int) $memberId]);
        $children = $db->select("SELECT * FROM children WHERE member_id = ? AND is_archived = 0 ORDER BY child_order", [(int) $memberId]);
        $temps = $db->select("SELECT * FROM temporary_members WHERE member_id = ? AND is_archived = 0 AND status = 'active' ORDER BY id", [(int) $memberId]);
        $fees = self::getFees();
        $familySub = self::calculateFamilyAnnualSub((int) $memberId, 0, array_column($spouses, 'id'));

        return $this->view('Death.Views.create', [
            'member'                => $member,
            'spouses'               => $spouses,
            'children'              => $children,
            'temps'                 => $temps,
            'form_fee'              => $fees['formFee'],
            'annual_sub'            => $familySub['annual_sub_total'],
            'total_fee'             => bcadd($fees['formFee'], $familySub['annual_sub_total'], 2),
            'family_sub'            => $familySub,
            'overdueSubscriptions'  => $overdueSubscriptions,
            'totalOverdue'          => $totalOverdue,
        ]);
    }

    public function store(Request $request, string $memberId): Response
    {
        $db = App::getInstance()->db();
        $member = $db->selectOne("SELECT * FROM members WHERE id = ? AND is_archived = 0", [(int) $memberId]);
        if (!$member) return $this->redirect('/members')->withError('العضو غير موجود');

        $deceasedType = trim($request->post('deceased_type', ''));
        $deathDate    = trim($request->post('death_date', ''));
        $certNumber   = trim($request->post('death_certificate_number', ''));
        $notes        = trim($request->post('notes', ''));

        if (!$deceasedType || !$deathDate) {
            return $this->redirect("/death/create/{$memberId}")->withError('بيانات الوفاة غير مكتملة');
        }

        $certPath = null;
        $inheritancePath = null;
        if ($deceasedType === 'primary_member') {
            if (empty($_FILES['death_certificate']['tmp_name'])) {
                return $this->redirect("/death/create/{$memberId}")->withError('يجب رفع شهادة الوفاة (PDF أو صورة)');
            }
            if (empty($_FILES['inheritance_notice']['tmp_name'])) {
                return $this->redirect("/death/create/{$memberId}")->withError('يجب رفع إعلام الوراثة (PDF أو صورة)');
            }

            $certUpload = self::uploadDocument($_FILES['death_certificate'], 'cert', (int) $memberId);
            if (!$certUpload['success']) {
                return $this->redirect("/death/create/{$memberId}")->withError('شهادة الوفاة: ' . $certUpload['error']);
            }

            $inheritanceUpload = self::uploadDocument($_FILES['inheritance_notice'], 'inh', (int) $memberId);
            if (!$inheritanceUpload['success']) {
                return $this->redirect("/death/create/{$memberId}")->withError('إعلام الوراثة: ' . $inheritanceUpload['error']);
            }

            $certPath        = $certUpload['path'];
            $inheritancePath = $inheritanceUpload['path'];
        }

        $initialStatus = ($deceasedType === 'primary_member') ? 'board_review' : 'recorded';

        $formNumber = ($deceasedType === 'primary_member') ? trim($request->post('form_number', '')) : '';

        $caseData = [
            'member_id'                => (int) $memberId,
            'deceased_type'            => $deceasedType,
            'death_date'               => $deathDate,
            'death_certificate_number' => $certNumber ?: null,
            'death_certificate_path'   => $certPath,
            'inheritance_notice_path'  => $inheritancePath,
            'same_membership_number'   => ($deceasedType === 'primary_member') ? 1 : 0,
            'fee_amount'               => '0.00',
            'status'                   => $initialStatus,
            'form_number'              => $formNumber ?: null,
            'notes'                    => $notes ?: null,
        ];

        if ($deceasedType === 'primary_member') {
            $primarySpouseId    = $request->post('primary_spouse_id') ? (int) $request->post('primary_spouse_id') : null;
            $secondarySpouseIds = $request->post('secondary_spouse_ids', []);
            $childrenAssignment = $request->post('children_assignment', []);
            $tempsAssignment    = $request->post('temps_assignment', []);

            if (!$primarySpouseId) {
                return $this->redirect("/death/create/{$memberId}")->withError('يجب اختيار الزوجة الأساسية لنقل العضوية');
            }

            // Calculate correct fee: primary spouse becomes member, exclude primary+secondaries from spouse count
            $fees = self::getFees();
            $familySub = self::calculateFamilyAnnualSub((int) $memberId, $primarySpouseId, $secondarySpouseIds);
            $totalFee = bcadd($fees['formFee'], $familySub['annual_sub_total'], 2);

            $caseData['spouse_id']               = $primarySpouseId;
            $caseData['primary_spouse_id']        = $primarySpouseId;
            $caseData['secondary_spouses_json']   = !empty($secondarySpouseIds) ? json_encode(array_map('intval', $secondarySpouseIds)) : null;
            $caseData['children_assignment_json'] = !empty($childrenAssignment) ? json_encode($childrenAssignment) : null;

            // Store temps assignment in notes JSON
            $notesPayload = [];
            if ($notes) $notesPayload['user_notes'] = $notes;
            if (!empty($tempsAssignment)) $notesPayload['temps_assignment'] = $tempsAssignment;
            if (!empty($notesPayload)) {
                $caseData['notes'] = json_encode($notesPayload, JSON_UNESCAPED_UNICODE);
            }

            $secondaryCount = count($secondarySpouseIds);
            if ($secondaryCount > 0) {
                $totalFee = bcmul($totalFee, (string) (1 + $secondaryCount), 2);
            }
            $caseData['fee_amount'] = $totalFee;
        } elseif ($deceasedType === 'spouse') {
            $spouseId = $request->post('spouse_id') ? (int) $request->post('spouse_id') : null;
            $caseData['spouse_id']  = $spouseId;
            $caseData['fee_amount'] = '0.00';
        } elseif ($deceasedType === 'child') {
            $childId = $request->post('child_id') ? (int) $request->post('child_id') : null;
            $caseData['child_id']   = $childId;
            $caseData['fee_amount'] = '0.00';
        }

        $case = DeathCase::create($caseData);

        EventBus::dispatch('death.recorded', ['case_id' => (int) $case->id, 'member_id' => (int) $memberId, 'type' => $deceasedType]);

        if ($deceasedType === 'primary_member') {
            return $this->redirect("/death/{$case->id}")->withSuccess('تم تسجيل حالة الوفاة وإرسالها لمراجعة مجلس الإدارة');
        }

        // Auto-complete child/spouse death (no fee, no further steps needed)
        // This archives the deceased and removes their subscriptions immediately
        return $this->complete($request, (string) $case->id);
    }

    public function show(Request $request, string $id): Response
    {
        $db = App::getInstance()->db();
        $case = $db->selectOne(
            "SELECT dc.*, m.full_name_ar as member_name, m.membership_number, m.membership_value as member_membership_value, m.created_at as membership_created_at, m.activated_at as membership_activated_at
             FROM death_cases dc JOIN members m ON m.id = dc.member_id WHERE dc.id = ?",
            [(int) $id]
        );
        if (!$case) return $this->redirect('/death')->withError('الحالة غير موجودة');

        $fees = self::getFees();
        $primarySpouse    = null;
        $secondarySpouses = [];
        $secondaryIds     = [];
        $newMember        = null;
        $paymentRequest   = null;
        $boardApprover    = null;
        $deceasedName     = null;

        if ($case['deceased_type'] === 'spouse' && !empty($case['spouse_id'])) {
            $deceasedRow = $db->selectOne("SELECT full_name_ar FROM spouses WHERE id = ?", [(int) $case['spouse_id']]);
            $deceasedName = $deceasedRow['full_name_ar'] ?? null;
        } elseif ($case['deceased_type'] === 'child' && !empty($case['child_id'])) {
            $deceasedRow = $db->selectOne("SELECT full_name_ar FROM children WHERE id = ?", [(int) $case['child_id']]);
            $deceasedName = $deceasedRow['full_name_ar'] ?? null;
        } elseif ($case['deceased_type'] === 'primary_member') {
            $deceasedName = $case['member_name'];
        }

        if ($case['primary_spouse_id']) {
            $primarySpouse = $db->selectOne("SELECT * FROM spouses WHERE id = ?", [(int) $case['primary_spouse_id']]);
        }
        if (!empty($case['secondary_spouses_json'])) {
            $secondaryIds = json_decode($case['secondary_spouses_json'], true) ?: [];
            if ($secondaryIds) {
                $placeholders = implode(',', array_fill(0, count($secondaryIds), '?'));
                $secondarySpouses = $db->select("SELECT * FROM spouses WHERE id IN ({$placeholders})", $secondaryIds);
            }
        }
        $familySub = self::calculateFamilyAnnualSub(
            (int) $case['member_id'],
            (int) ($case['primary_spouse_id'] ?? 0),
            $secondaryIds
        );
        if ($case['transferred_to_member_id']) {
            $newMember = $db->selectOne("SELECT id, full_name_ar, membership_number, status, created_at, activated_at FROM members WHERE id = ?", [(int) $case['transferred_to_member_id']]);
        }
        if ($case['payment_request_id']) {
            $paymentRequest = $db->selectOne("SELECT * FROM payment_requests WHERE id = ?", [(int) $case['payment_request_id']]);
        }
        if ($case['board_approved_by']) {
            $boardApprover = $db->selectOne("SELECT full_name_ar FROM employees WHERE id = ?", [(int) $case['board_approved_by']]);
        }

        // Get current plan price for JS preview (not the old membership_value)
        $currentPlanPrice = '0.00';
        $memberRow = $db->selectOne("SELECT branch_id, qualification_id, membership_type FROM members WHERE id = ?", [(int) $case['member_id']]);
        if ($memberRow) {
            $brId = (int) ($memberRow['branch_id'] ?? 1);
            $qId = !empty($memberRow['qualification_id']) ? (int) $memberRow['qualification_id'] : null;
            if ($qId) {
                $qual = $db->selectOne("SELECT code FROM qualifications WHERE id = ?", [$qId]);
                if ($qual && $qual['code']) {
                    $priceInfo = \App\Modules\Pricing\Services\PricingEngine::getMembershipPrice($brId, $qual['code']);
                    if (!empty($priceInfo['price']) && bccomp($priceInfo['price'], '0.01', 2) >= 0) {
                        $currentPlanPrice = $priceInfo['price'];
                    }
                }
            }
            if (bccomp($currentPlanPrice, '0.01', 2) < 0) {
                $pr = $db->selectOne(
                    "SELECT price FROM pricing_configs WHERE branch_id = ? AND is_active = 1 AND effective_from <= CURDATE() AND (effective_to IS NULL OR effective_to >= CURDATE()) ORDER BY price DESC LIMIT 1",
                    [$brId]
                );
                if ($pr) $currentPlanPrice = $pr['price'];
            }
            if (bccomp($currentPlanPrice, '0.01', 2) < 0) {
                $pr = $db->selectOne("SELECT price FROM pricing_configs WHERE is_active = 1 ORDER BY price DESC LIMIT 1");
                if ($pr) $currentPlanPrice = $pr['price'];
            }
        }

        $newMemberSubs = [];
        if ($newMember && $case['status'] === 'completed') {
            $newMemberSubs = $db->select(
                "SELECT person_type, person_name, total_amount, status, financial_year FROM subscriptions WHERE member_id = ? ORDER BY financial_year DESC, person_type ASC",
                [(int) $newMember['id']]
            );
        }

        return $this->view('Death.Views.show', [
            'case'             => $case,
            'fees'             => $fees,
            'familySub'        => $familySub,
            'primarySpouse'    => $primarySpouse,
            'secondarySpouses' => $secondarySpouses,
            'newMember'        => $newMember,
            'newMemberSubs'    => $newMemberSubs,
            'paymentRequest'   => $paymentRequest,
            'boardApprover'    => $boardApprover,
            'currentPlanPrice' => $currentPlanPrice,
            'deceasedName'     => $deceasedName,
        ]);
    }

    public function boardApprove(Request $request, string $id): Response
    {
        $this->authorize('transfer.approve');
        $db       = App::getInstance()->db();
        $employee = App::getInstance()->currentEmployee();

        $case = $db->selectOne("SELECT * FROM death_cases WHERE id = ?", [(int) $id]);
        if (!$case) return $this->redirect('/death')->withError('الحالة غير موجودة');
        if ($case['status'] !== 'board_review') {
            return $this->redirect("/death/{$id}")->withError('الحالة ليست في مرحلة مراجعة مجلس الإدارة');
        }

        $feeMethod    = trim($request->post('fee_method', ''));
        $feeValue     = trim($request->post('fee_value', '0'));
        $boardRef     = trim($request->post('board_decision_reference', ''));
        $boardDate    = trim($request->post('board_decision_date', ''));
        $boardNotes   = trim($request->post('board_notes', ''));

        if (!in_array($feeMethod, ['percentage', 'fixed'], true)) {
            return $this->redirect("/death/{$id}")->withError('يجب تحديد طريقة احتساب رسوم مجلس الأمناء');
        }
        if (!is_numeric($feeValue) || bccomp($feeValue, '0', 4) < 0) {
            return $this->redirect("/death/{$id}")->withError('قيمة رسوم مجلس الأمناء غير صحيحة');
        }

        // Trustee fee: percentage is applied to CURRENT membership plan price (not old stored value)
        $memberRow = $db->selectOne("SELECT * FROM members WHERE id = ?", [(int) $case['member_id']]);
        $boardFeeAmount = '0.00';
        $currentPlanPrice = '0.00';
        if ($feeMethod === 'percentage') {
            $branchId = (int) ($memberRow['branch_id'] ?? 1);
            $qualId = !empty($memberRow['qualification_id']) ? (int) $memberRow['qualification_id'] : null;

            // Try via PricingEngine with qualification
            if ($qualId) {
                $qual = $db->selectOne("SELECT code FROM qualifications WHERE id = ?", [$qualId]);
                if ($qual && $qual['code']) {
                    $priceInfo = \App\Modules\Pricing\Services\PricingEngine::getMembershipPrice($branchId, $qual['code']);
                    if (!empty($priceInfo['price']) && bccomp($priceInfo['price'], '0.01', 2) >= 0) {
                        $currentPlanPrice = $priceInfo['price'];
                    }
                }
            }

            // Fallback 1: query pricing_configs by branch + qualification_id directly
            if (bccomp($currentPlanPrice, '0.01', 2) < 0 && $qualId) {
                $pricing = $db->selectOne(
                    "SELECT price FROM pricing_configs WHERE branch_id = ? AND qualification_id = ? AND is_active = 1 AND effective_from <= CURDATE() AND (effective_to IS NULL OR effective_to >= CURDATE()) ORDER BY effective_from DESC LIMIT 1",
                    [$branchId, $qualId]
                );
                if ($pricing && bccomp($pricing['price'], '0.01', 2) >= 0) {
                    $currentPlanPrice = $pricing['price'];
                }
            }

            // Fallback 2: any active pricing for this branch (highest price = working member)
            if (bccomp($currentPlanPrice, '0.01', 2) < 0) {
                $pricing = $db->selectOne(
                    "SELECT price FROM pricing_configs WHERE branch_id = ? AND is_active = 1 AND effective_from <= CURDATE() AND (effective_to IS NULL OR effective_to >= CURDATE()) ORDER BY price DESC LIMIT 1",
                    [$branchId]
                );
                if ($pricing && bccomp($pricing['price'], '0.01', 2) >= 0) {
                    $currentPlanPrice = $pricing['price'];
                }
            }

            // Fallback 3: any active pricing across all branches (last resort before membership_value)
            if (bccomp($currentPlanPrice, '0.01', 2) < 0) {
                $pricing = $db->selectOne(
                    "SELECT price FROM pricing_configs WHERE is_active = 1 AND effective_from <= CURDATE() AND (effective_to IS NULL OR effective_to >= CURDATE()) ORDER BY price DESC LIMIT 1"
                );
                if ($pricing && bccomp($pricing['price'], '0.01', 2) >= 0) {
                    $currentPlanPrice = $pricing['price'];
                }
            }

            // Last fallback: stored membership_value (should never reach here if pricing_configs has data)
            if (bccomp($currentPlanPrice, '0.01', 2) < 0) {
                $currentPlanPrice = $memberRow['membership_value'] ?? '0.00';
            }

            $boardFeeAmount = bcdiv(bcmul($currentPlanPrice, $feeValue, 6), '100', 2);
        } else {
            $boardFeeAmount = bcadd($feeValue, '0', 2);
        }

        // Calculate family annual subscription (primary spouse becomes member, exclude secondaries)
        $fees = self::getFees();
        $secondaryIds = !empty($case['secondary_spouses_json']) ? (json_decode($case['secondary_spouses_json'], true) ?: []) : [];
        $familySub = self::calculateFamilyAnnualSub((int) $case['member_id'], (int) ($case['primary_spouse_id'] ?? 0), $secondaryIds);
        $annualSubTotal = $familySub['annual_sub_total'];

        $secondaryCount = 0;
        if (!empty($case['secondary_spouses_json'])) {
            $secondaryCount = count(json_decode($case['secondary_spouses_json'], true));
        }

        // Base fees per membership = form_fee + family annual subscription
        $basePerMembership = bcadd($fees['formFee'], $annualSubTotal, 2);
        $baseFees = bcmul($basePerMembership, (string) (1 + $secondaryCount), 2);
        $totalFee = bcadd($baseFees, $boardFeeAmount, 2);

        $db->update('death_cases', [
            'board_fee_method'         => $feeMethod,
            'board_fee_value'          => $feeValue,
            'board_fee_amount'         => $boardFeeAmount,
            'board_decision_reference' => $boardRef ?: null,
            'board_decision_date'      => $boardDate ?: null,
            'board_notes'              => $boardNotes ?: null,
            'board_approved_by'        => $employee ? (int) $employee->id : null,
            'board_approved_at'        => date('Y-m-d H:i:s'),
            'fee_amount'               => $totalFee,
            'status'                   => 'board_approved',
            'updated_at'               => date('Y-m-d H:i:s'),
        ], '`id` = ?', [(int) $id]);

        // Build fee breakdown
        $multiplierLabel = $secondaryCount > 0 ? " (× " . (1 + $secondaryCount) . " عضويات)" : '';
        $breakdown = [
            'رسوم نقل العضوية' . $multiplierLabel . ':',
            '  رسوم استمارة: ' . money($fees['formFee']),
        ];
        if (!empty($familySub['current_fy_covered'])) {
            $breakdown[] = '  اشتراك سنوي: معفى (السنة المالية الحالية مغطاة — المتوفى سدد/مسجل بالفعل)';
        } else {
            $breakdown[] = '  اشتراك سنوي (العضوية الجديدة): ' . money($annualSubTotal);
            $breakdown[] = '    - العضو الجديد (الزوجة): ' . money($familySub['member_sub']);
            if ($familySub['spouse_count'] > 0) {
                $breakdown[] = '    - ' . $familySub['spouse_count'] . ' زوجة إضافية: ' . money(bcmul($familySub['spouse_sub'], (string) $familySub['spouse_count'], 2));
            }
            if ($familySub['child_count'] > 0) {
                $breakdown[] = '    - ' . $familySub['child_count'] . ' أبناء: ' . money(bcmul($familySub['child_sub'], (string) $familySub['child_count'], 2));
            }
            if ($familySub['temp_count'] > 0) {
                $breakdown[] = '    - ' . $familySub['temp_count'] . ' مؤقتين: ' . money(bcmul($familySub['temp_sub'], (string) $familySub['temp_count'], 2));
            }
            $breakdown[] = '    - رسوم تنمية: ' . money($familySub['dev_fee']);
        }
        if ($secondaryCount > 0) {
            $breakdown[] = '  مجموع الأساس (' . (1 + $secondaryCount) . ' عضويات): ' . money($baseFees);
        }
        $trusteeLabel = $feeMethod === 'percentage'
            ? 'رسوم مجلس الأمناء (' . $feeValue . '% من قيمة الخطة الحالية ' . money($currentPlanPrice) . ')'
            : 'رسوم مجلس الأمناء (مبلغ ثابت)';
        $breakdown[] = $trusteeLabel . ': ' . money($boardFeeAmount);
        $breakdown[] = '═══════════════════════════';
        $breakdown[] = 'الإجمالي: ' . money($totalFee);

        // Payment request uses the PRIMARY SPOUSE name (the person receiving the membership)
        $primarySpouse = $db->selectOne("SELECT full_name_ar FROM spouses WHERE id = ?", [(int) $case['primary_spouse_id']]);
        $recipientName = $primarySpouse['full_name_ar'] ?? 'الزوجة';

        $result = PaymentRequestService::createRequest([
            'member_id'           => (int) $case['member_id'],
            'amount'              => $totalFee,
            'payment_type'        => 'death_fee',
            'related_entity_type' => 'death_cases',
            'related_entity_id'   => (int) $id,
            'description_ar'      => 'رسوم نقل عضوية (وفاة) — باسم: ' . $recipientName . ' — حالة #' . $id,
            'notes'               => json_encode([
                'fee_breakdown'   => $breakdown,
                'recipient_name'  => $recipientName,
                'form_number'     => $case['form_number'] ?? null,
                'form_fee'        => $fees['formFee'],
                'annual_sub'      => $annualSubTotal,
                'trustee_fee'     => $boardFeeAmount,
                'trustee_method'  => $feeMethod,
                'multiplier'      => 1 + $secondaryCount,
                'total'           => $totalFee,
            ], JSON_UNESCAPED_UNICODE),
        ]);

        if ($result['success']) {
            $db->update('death_cases', [
                'payment_request_id' => $result['request_id'],
                'updated_at'         => date('Y-m-d H:i:s'),
            ], '`id` = ?', [(int) $id]);
        }

        return $this->redirect("/death/{$id}")->withSuccess(
            'تم اعتماد القضية من مجلس الإدارة — رسوم مجلس الأمناء: ' . money($boardFeeAmount) . ' — الإجمالي: ' . money($totalFee)
        );
    }

    public function boardReject(Request $request, string $id): Response
    {
        $this->authorize('transfer.approve');
        $db       = App::getInstance()->db();
        $employee = App::getInstance()->currentEmployee();

        $case = $db->selectOne("SELECT id, status FROM death_cases WHERE id = ?", [(int) $id]);
        if (!$case) return $this->redirect('/death')->withError('الحالة غير موجودة');
        if ($case['status'] !== 'board_review') {
            return $this->redirect("/death/{$id}")->withError('الحالة ليست في مرحلة مراجعة مجلس الإدارة');
        }

        $boardNotes = trim($request->post('board_notes', ''));

        $db->update('death_cases', [
            'status'           => 'rejected',
            'board_notes'      => $boardNotes ?: null,
            'board_approved_by'=> $employee ? (int) $employee->id : null,
            'board_approved_at'=> date('Y-m-d H:i:s'),
            'updated_at'       => date('Y-m-d H:i:s'),
        ], '`id` = ?', [(int) $id]);

        return $this->redirect("/death/{$id}")->withWarning('تم رفض القضية من مجلس الإدارة');
    }

    public function downloadDocument(Request $request, string $id, string $type): Response
    {
        $this->authorize('transfer.view');
        $db = App::getInstance()->db();

        $case = $db->selectOne("SELECT death_certificate_path, inheritance_notice_path FROM death_cases WHERE id = ?", [(int) $id]);
        if (!$case) return $this->redirect('/death')->withError('الحالة غير موجودة');

        $column = match($type) {
            'cert'        => 'death_certificate_path',
            'inheritance' => 'inheritance_notice_path',
            default       => null,
        };

        if (!$column || empty($case[$column])) {
            return $this->redirect("/death/{$id}")->withError('المستند غير موجود');
        }

        $filePath = App::getInstance()->basePath() . '/' . $case[$column];
        if (!file_exists($filePath)) {
            return $this->redirect("/death/{$id}")->withError('ملف المستند غير موجود على الخادم');
        }

        $finfo    = new \finfo(FILEINFO_MIME_TYPE);
        $mime     = $finfo->file($filePath) ?: 'application/octet-stream';
        $filename = basename($filePath);

        header('Content-Type: ' . $mime);
        header('Content-Disposition: inline; filename="' . $filename . '"');
        header('Content-Length: ' . filesize($filePath));
        header('Cache-Control: private, max-age=3600');
        readfile($filePath);
        exit;
    }

    public function pay(Request $request, string $id): Response
    {
        $db = App::getInstance()->db();
        $case = $db->selectOne("SELECT * FROM death_cases WHERE id = ?", [(int) $id]);
        if (!$case) return $this->redirect('/death')->withError('الحالة غير موجودة');
        if (in_array($case['status'], ['completed', 'fee_paid', 'pending_form_fill'], true)) {
            return $this->redirect("/death/{$id}")->withError('تم دفع الرسوم مسبقاً');
        }

        $amount = $case['fee_amount'] ?? '0.00';
        if (bccomp((string) $amount, '0', 2) <= 0) {
            return $this->redirect("/death/{$id}")->withError('لا توجد رسوم مطلوبة');
        }

        $result = PaymentService::processPayment([
            'member_id'           => (int) $case['member_id'],
            'amount'              => $amount,
            'payment_type'        => 'death_fee',
            'payment_method'      => $request->post('payment_method', 'cash'),
            'related_entity_type' => 'death_cases',
            'related_entity_id'   => (int) $id,
            'description'         => 'رسوم وفاة — حالة #' . $id,
        ]);

        if (!$result['success']) {
            return $this->redirect("/death/{$id}")->withError($result['error']);
        }

        $newStatus = ($case['deceased_type'] === 'primary_member') ? 'pending_form_fill' : 'fee_paid';

        $db->update('death_cases', [
            'status'     => $newStatus,
            'updated_at' => date('Y-m-d H:i:s'),
        ], '`id` = ?', [(int) $id]);

        EventBus::dispatch('death.fee_paid', ['case_id' => (int) $id, 'payment_id' => $result['payment_id']]);

        if ($newStatus === 'pending_form_fill') {
            return $this->redirect("/death/{$id}")->withSuccess('تم دفع الرسوم — الآن يجب ملء استمارة العضوية الجديدة للزوجة');
        }

        return $this->redirect("/death/{$id}")->withSuccess('تم دفع الرسوم — إيصال: ' . $result['receipt_number']);
    }

    public function fillForm(Request $request, string $id): Response
    {
        $db = App::getInstance()->db();
        $case = $db->selectOne("SELECT * FROM death_cases WHERE id = ?", [(int) $id]);
        if (!$case || $case['status'] !== 'pending_form_fill') {
            return $this->redirect("/death/{$id}")->withError('لا يمكن ملء الاستمارة في هذه المرحلة');
        }

        $spouse = $db->selectOne("SELECT * FROM spouses WHERE id = ?", [(int) $case['primary_spouse_id']]);
        $member = $db->selectOne("SELECT * FROM members WHERE id = ?", [(int) $case['member_id']]);

        return $this->view('Death.Views.form_fill', [
            'case'           => $case,
            'spouse'         => $spouse,
            'member'         => $member,
            'qualifications' => $db->select("SELECT id, name_ar FROM qualifications WHERE is_active = 1 ORDER BY sort_order"),
            'governorates'   => $db->select("SELECT code, name_ar FROM governorates WHERE is_active = 1 ORDER BY name_ar"),
            'countries'      => $db->select("SELECT nationality_ar FROM countries WHERE is_active = 1 ORDER BY name_ar"),
        ]);
    }

    public function saveFillForm(Request $request, string $id): Response
    {
        $db = App::getInstance()->db();
        $case = $db->selectOne("SELECT * FROM death_cases WHERE id = ?", [(int) $id]);
        if (!$case || $case['status'] !== 'pending_form_fill') {
            return $this->redirect("/death/{$id}")->withError('لا يمكن ملء الاستمارة في هذه المرحلة');
        }

        $data = $request->all();
        unset($data['_csrf_token']);

        $nid = trim((string) ($data['national_id'] ?? ''));
        $data['national_id'] = $nid;
        if ($nid !== '' && strlen($nid) === 14) {
            $parsed = NationalIdParser::parse($nid);
            if ($parsed['is_valid']) {
                $data['date_of_birth'] = $parsed['dob'];
                $data['gender']        = $parsed['gender'];
            }
        }

        $requiredFields = ['full_name_ar', 'national_id', 'date_of_birth', 'gender', 'qualification_id'];
        foreach ($requiredFields as $field) {
            if (empty($data[$field])) {
                return $this->redirect("/death/{$id}/fill-form")->withError("الحقل {$field} مطلوب");
            }
        }

        // Merge with existing notes (temps_assignment etc)
        $existingNotes = !empty($case['notes']) ? json_decode($case['notes'], true) : [];
        if (!is_array($existingNotes)) $existingNotes = [];
        $existingNotes['form_data'] = $data;

        $db->update('death_cases', [
            'primary_spouse_form_filled' => 1,
            'notes'                      => json_encode($existingNotes, JSON_UNESCAPED_UNICODE),
            'updated_at'                 => date('Y-m-d H:i:s'),
        ], '`id` = ?', [(int) $id]);

        // Auto-complete the death case after form fill
        return $this->complete($request, $id);
    }

    public function complete(Request $request, string $id): Response
    {
        $db       = App::getInstance()->db();
        $case     = $db->selectOne("SELECT * FROM death_cases WHERE id = ?", [(int) $id]);
        if (!$case || $case['status'] === 'completed') return $this->redirect('/death')->withError('الحالة غير صالحة');

        $employee = App::getInstance()->currentEmployee();
        $caseId = (int) $id;

        if ($case['deceased_type'] === 'primary_member') {
            if (!$case['board_approved_at']) {
                return $this->redirect("/death/{$id}")->withError('لم يتم اعتماد القضية من مجلس الإدارة بعد');
            }
            if (!$case['death_certificate_path'] || !$case['inheritance_notice_path']) {
                return $this->redirect("/death/{$id}")->withError('يجب رفع وثيقة الوفاة وإعلام الوراثة قبل الإتمام');
            }
            $deathPaymentCheck = $db->selectOne(
                "SELECT id FROM payments WHERE member_id = ? AND payment_type = 'death_fee'
                 AND related_entity_type = 'death_cases' AND related_entity_id = ? AND is_voided = 0",
                [(int) $case['member_id'], $caseId]
            );
            if (!$deathPaymentCheck) {
                return $this->redirect("/death/{$id}")->withError('لم يتم سداد رسوم الوفاة في الخزينة بعد');
            }
            if (!$case['primary_spouse_form_filled']) {
                return $this->redirect("/death/{$id}")->withError('يجب ملء استمارة العضوية الجديدة للزوجة أولاً');
            }
        }

        $db->beginTransaction();
        try {
            $snapshotId = ArchiveService::takeSnapshot('members', (int) $case['member_id'], 'death', 'وفاة — حالة #' . $id);
            $auditLog = [];

            DeathAuditService::log($caseId, 'snapshot_taken', 'تم أخذ لقطة أرشيفية كاملة للعضوية الأصلية', 'archive_snapshots', $snapshotId);

            if ($case['deceased_type'] === 'spouse' && $case['spouse_id']) {
                $spouseRow = $db->selectOne("SELECT full_name_ar FROM spouses WHERE id = ?", [(int) $case['spouse_id']]);
                $db->update('spouses', [
                    'status' => 'deceased', 'is_archived' => 1,
                    'archived_at' => date('Y-m-d H:i:s'),
                ], '`id` = ?', [(int) $case['spouse_id']]);
                // Remove unpaid subscription rows for the deceased spouse
                $db->query(
                    "DELETE FROM subscriptions WHERE member_id = ? AND person_type = 'spouse' AND person_id = ? AND status IN ('pending','overdue')",
                    [(int) $case['member_id'], (int) $case['spouse_id']]
                );
                $auditLog[] = 'تم تسجيل وفاة الزوج/ة: ' . ($spouseRow['full_name_ar'] ?? '');
                DeathAuditService::log($caseId, 'spouse_deceased', 'تم تسجيل وفاة الزوج/ة: ' . ($spouseRow['full_name_ar'] ?? ''), 'spouses', (int) $case['spouse_id']);

            } elseif ($case['deceased_type'] === 'child' && $case['child_id']) {
                $childRow = $db->selectOne("SELECT full_name_ar FROM children WHERE id = ?", [(int) $case['child_id']]);
                $db->update('children', [
                    'status' => 'deceased', 'is_archived' => 1,
                    'archived_at' => date('Y-m-d H:i:s'),
                ], '`id` = ?', [(int) $case['child_id']]);
                // Remove unpaid subscription rows for the deceased child
                $db->query(
                    "DELETE FROM subscriptions WHERE member_id = ? AND person_type = 'child' AND person_id = ? AND status IN ('pending','overdue')",
                    [(int) $case['member_id'], (int) $case['child_id']]
                );
                $auditLog[] = 'تم تسجيل وفاة الابن/الابنة: ' . ($childRow['full_name_ar'] ?? '');
                DeathAuditService::log($caseId, 'child_deceased', 'تم تسجيل وفاة الابن/الابنة: ' . ($childRow['full_name_ar'] ?? ''), 'children', (int) $case['child_id']);

            } elseif ($case['deceased_type'] === 'primary_member') {
                $member = $db->selectOne("SELECT * FROM members WHERE id = ?", [(int) $case['member_id']]);
                $spouse = $db->selectOne("SELECT * FROM spouses WHERE id = ?", [(int) $case['primary_spouse_id']]);

                $formData = [];
                $notesData = !empty($case['notes']) ? json_decode($case['notes'], true) : [];
                if (is_array($notesData)) {
                    $formData = $notesData['form_data'] ?? [];
                }
                $tempsAssignment = $notesData['temps_assignment'] ?? [];

                $inheritedNumber = $member['membership_number'];
                $auditLog[] = 'العضو المتوفى: ' . $member['full_name_ar'] . ' — رقم العضوية: ' . $inheritedNumber;

                $deathPayment = $db->selectOne(
                    "SELECT id FROM payments WHERE member_id = ? AND payment_type = 'death_fee'
                     AND related_entity_type = 'death_cases' AND related_entity_id = ? AND is_voided = 0
                     ORDER BY id DESC LIMIT 1",
                    [(int) $case['member_id'], $caseId]
                );
                $deathPaymentId = $deathPayment ? (int) $deathPayment['id'] : null;

                // STEP 1: Archive deceased member (preserve all original data, only change status fields)
                $db->update('members', [
                    'is_archived'       => 1,
                    'archived_at'       => date('Y-m-d H:i:s'),
                    'status'            => 'deceased',
                    'membership_number' => null,
                ], '`id` = ?', [(int) $case['member_id']]);

                DeathAuditService::log($caseId, 'member_archived', 'تم أرشفة العضوية الأصلية — العضو: ' . $member['full_name_ar'] . ' — رقم: ' . $inheritedNumber, 'members', (int) $case['member_id']);

                // STEP 2: Create new primary member with current plan price
                $currentPlanPrice = $this->getCurrentPlanPrice($db, $member);

                $newMemberData = [
                    'membership_number'          => $inheritedNumber,
                    'full_name_ar'               => $formData['full_name_ar'] ?? $spouse['full_name_ar'],
                    'full_name_en'               => $formData['full_name_en'] ?? $spouse['full_name_en'] ?? null,
                    'national_id'                => $formData['national_id'] ?? $spouse['national_id'],
                    'date_of_birth'              => $formData['date_of_birth'] ?? $spouse['date_of_birth'] ?? '1900-01-01',
                    'gender'                     => $formData['gender'] ?? $spouse['gender'] ?? 'female',
                    'nationality'                => $formData['nationality'] ?? $spouse['nationality'] ?? 'مصري',
                    'branch_id'                  => (int) $member['branch_id'],
                    'membership_type'            => $member['membership_type'] ?? 'working',
                    'member_category'            => 'working_member',
                    'status'                     => 'active',
                    'activated_at'               => date('Y-m-d H:i:s'),
                    'activated_by_payment_id'    => $deathPaymentId,
                    'qualification_id'           => !empty($formData['qualification_id']) ? (int) $formData['qualification_id'] : ($spouse['qualification_id'] ?? $member['qualification_id']),
                    'phone_mobile'               => $formData['phone_mobile'] ?? $spouse['mobile'] ?? $member['phone_mobile'],
                    'membership_value'           => $currentPlanPrice,
                    'transferred_from_death_id'  => $caseId,
                    'original_membership_number' => $inheritedNumber,
                    'ownership_transfer_date'    => date('Y-m-d'),
                    'membership_origin_date'     => $member['membership_origin_date'] ?? $member['form_date'] ?? substr($member['created_at'], 0, 10),
                    'form_number'                => $formData['form_number'] ?? null,
                    'deceased_member_name'       => $member['full_name_ar'],
                    'created_at'                 => date('Y-m-d H:i:s'),
                    'updated_at'                 => date('Y-m-d H:i:s'),
                    'created_by'                 => $employee ? (int) $employee->id : null,
                ];

                $optionalFields = ['marital_status', 'religion', 'phone_home', 'email', 'emergency_name', 'emergency_phone', 'residence_address', 'area', 'governorate', 'occupation', 'job_title'];
                foreach ($optionalFields as $field) {
                    if (!empty($formData[$field])) {
                        $newMemberData[$field] = $formData[$field];
                    }
                }

                $newMemberId = $db->insert('members', $newMemberData);
                $auditLog[] = 'تم تحويل العضوية إلى: ' . ($formData['full_name_ar'] ?? $spouse['full_name_ar']) . ' — رقم العضوية: ' . $inheritedNumber;

                DeathAuditService::log($caseId, 'new_member_created', 'تم إنشاء عضوية جديدة باسم: ' . ($formData['full_name_ar'] ?? $spouse['full_name_ar']) . ' — رقم: ' . $inheritedNumber, 'members', $newMemberId, [
                    'membership_value' => $currentPlanPrice,
                    'inherited_number' => $inheritedNumber,
                ]);

                // Archive primary spouse
                $db->update('spouses', [
                    'status' => 'transferred', 'is_archived' => 1,
                    'archived_at' => date('Y-m-d H:i:s'),
                ], '`id` = ?', [(int) $case['primary_spouse_id']]);

                // Handle children assignment
                $childrenAssignment = !empty($case['children_assignment_json']) ? json_decode($case['children_assignment_json'], true) : [];
                $childrenMoved = [];
                if (!empty($childrenAssignment)) {
                    foreach ($childrenAssignment as $childId => $assignTo) {
                        if ($assignTo === 'primary') {
                            $db->update('children', ['member_id' => $newMemberId, 'updated_at' => date('Y-m-d H:i:s')],
                                '`id` = ? AND `is_archived` = 0', [(int) $childId]);
                            $childRow = $db->selectOne("SELECT full_name_ar FROM children WHERE id = ?", [(int) $childId]);
                            if ($childRow) $childrenMoved[] = $childRow['full_name_ar'];
                        }
                    }
                } else {
                    $db->update('children', ['member_id' => $newMemberId, 'updated_at' => date('Y-m-d H:i:s')],
                        '`member_id` = ? AND `is_archived` = 0', [(int) $case['member_id']]);
                }

                if (!empty($childrenMoved)) {
                    $auditLog[] = 'تم نقل الأبناء التاليين إلى العضوية الجديدة: ' . implode('، ', $childrenMoved);
                    DeathAuditService::log($caseId, 'children_transferred', 'تم نقل الأبناء: ' . implode('، ', $childrenMoved), 'members', $newMemberId);
                } elseif (empty($childrenAssignment)) {
                    $auditLog[] = 'تم نقل جميع الأبناء إلى العضوية الجديدة';
                    DeathAuditService::log($caseId, 'children_transferred', 'تم نقل جميع الأبناء إلى العضوية الجديدة', 'members', $newMemberId);
                }

                // Handle secondary spouses
                $secondaryMemberIds = [];
                if (!empty($case['secondary_spouses_json'])) {
                    $secondaryIds = json_decode($case['secondary_spouses_json'], true);
                    foreach ($secondaryIds as $secSpouseId) {
                        $secSpouse = $db->selectOne("SELECT * FROM spouses WHERE id = ?", [(int) $secSpouseId]);
                        if (!$secSpouse) continue;

                        $secMemberId = $db->insert('members', [
                            'full_name_ar'               => $secSpouse['full_name_ar'],
                            'full_name_en'               => $secSpouse['full_name_en'] ?? null,
                            'national_id'                => $secSpouse['national_id'],
                            'date_of_birth'              => !empty($secSpouse['date_of_birth']) ? $secSpouse['date_of_birth'] : '1900-01-01',
                            'gender'                     => $secSpouse['gender'] ?? 'female',
                            'nationality'                => $secSpouse['nationality'] ?? 'مصري',
                            'branch_id'                  => (int) $member['branch_id'],
                            'membership_type'            => $member['membership_type'] ?? 'working',
                            'member_category'            => 'working_member',
                            'status'                     => 'active',
                            'activated_at'               => date('Y-m-d H:i:s'),
                            'activated_by_payment_id'    => $deathPaymentId,
                            'phone_mobile'               => $secSpouse['mobile'] ?? $member['phone_mobile'] ?? '00000000000',
                            'membership_value'           => $currentPlanPrice,
                            'transferred_from_death_id'  => $caseId,
                            'ownership_transfer_date'    => date('Y-m-d'),
                            'deceased_member_name'       => $member['full_name_ar'],
                            'created_at'                 => date('Y-m-d H:i:s'),
                            'updated_at'                 => date('Y-m-d H:i:s'),
                            'created_by'                 => $employee ? (int) $employee->id : null,
                        ]);

                        \App\Modules\Members\Services\MemberNumberGenerator::assign($secMemberId);
                        $secondaryMemberIds[(int) $secSpouseId] = $secMemberId;

                        $db->update('spouses', [
                            'status' => 'transferred', 'is_archived' => 1,
                            'archived_at' => date('Y-m-d H:i:s'),
                        ], '`id` = ?', [(int) $secSpouseId]);

                        $secNumber = $db->selectOne("SELECT membership_number FROM members WHERE id = ?", [$secMemberId]);
                        $auditLog[] = 'تم إنشاء عضوية منفصلة للزوجة: ' . $secSpouse['full_name_ar'] . ' — رقم: ' . ($secNumber['membership_number'] ?? '');
                        DeathAuditService::log($caseId, 'secondary_member_created', 'تم إنشاء عضوية منفصلة للزوجة: ' . $secSpouse['full_name_ar'] . ' — رقم: ' . ($secNumber['membership_number'] ?? ''), 'members', $secMemberId);

                        // Move children assigned to this secondary spouse
                        if (!empty($childrenAssignment)) {
                            foreach ($childrenAssignment as $childId => $assignTo) {
                                if ((int) $assignTo === (int) $secSpouseId) {
                                    $db->update('children', ['member_id' => $secMemberId, 'updated_at' => date('Y-m-d H:i:s')],
                                        '`id` = ? AND `is_archived` = 0', [(int) $childId]);
                                    $childRow = $db->selectOne("SELECT full_name_ar FROM children WHERE id = ?", [(int) $childId]);
                                    if ($childRow) {
                                        $auditLog[] = 'تم نقل ' . $childRow['full_name_ar'] . ' إلى عضوية ' . $secSpouse['full_name_ar'];
                                    }
                                }
                            }
                        }
                    }
                }

                // Sweep remaining children to primary
                $db->query(
                    "UPDATE children SET member_id = ?, updated_at = ? WHERE member_id = ? AND is_archived = 0",
                    [$newMemberId, date('Y-m-d H:i:s'), (int) $case['member_id']]
                );

                // Renumber child_order
                $allChildren = $db->select(
                    "SELECT id FROM children WHERE member_id = ? AND is_archived = 0 ORDER BY child_order, id",
                    [$newMemberId]
                );
                foreach ($allChildren as $i => $c) {
                    $db->update('children', [
                        'child_order'             => $i + 1,
                        'activated_by_payment_id' => $deathPaymentId,
                        'updated_at'              => date('Y-m-d H:i:s'),
                    ], '`id` = ?', [(int) $c['id']]);
                }

                // Transfer temporary members based on assignment
                $tempsMoved = [];
                if (!empty($tempsAssignment)) {
                    foreach ($tempsAssignment as $tempId => $assignTo) {
                        $targetMemberId = $newMemberId;
                        if ($assignTo !== 'primary' && isset($secondaryMemberIds[(int) $assignTo])) {
                            $targetMemberId = $secondaryMemberIds[(int) $assignTo];
                        }
                        $db->update('temporary_members', ['member_id' => $targetMemberId, 'updated_at' => date('Y-m-d H:i:s')],
                            '`id` = ? AND `is_archived` = 0', [(int) $tempId]);
                        $tempRow = $db->selectOne("SELECT full_name_ar FROM temporary_members WHERE id = ?", [(int) $tempId]);
                        if ($tempRow) $tempsMoved[] = $tempRow['full_name_ar'];
                    }
                }
                // Sweep remaining temps to primary
                $db->query(
                    "UPDATE temporary_members SET member_id = ?, updated_at = ? WHERE member_id = ? AND is_archived = 0",
                    [$newMemberId, date('Y-m-d H:i:s'), (int) $case['member_id']]
                );

                if (!empty($tempsMoved)) {
                    $auditLog[] = 'تم نقل الأعضاء المؤقتين: ' . implode('، ', $tempsMoved);
                    DeathAuditService::log($caseId, 'temps_transferred', 'تم نقل الأعضاء المؤقتين: ' . implode('، ', $tempsMoved), 'members', $newMemberId);
                } else {
                    $tempCount = (int) ($db->selectOne("SELECT COUNT(*) as cnt FROM temporary_members WHERE member_id = ? AND is_archived = 0", [$newMemberId])['cnt'] ?? 0);
                    if ($tempCount > 0) {
                        $auditLog[] = 'تم نقل ' . $tempCount . ' عضو مؤقت إلى العضوية الجديدة';
                        DeathAuditService::log($caseId, 'temps_transferred', 'تم نقل ' . $tempCount . ' عضو مؤقت إلى العضوية الجديدة', 'members', $newMemberId);
                    }
                }

                // STEP 3: Transfer active financial data to new membership
                $this->transferActiveData($db, (int) $case['member_id'], $newMemberId, $caseId);

                // STEP 4: Create annual subscriptions for new membership (marked as paid — fee collected with death fee)
                // If current FY was already covered by the deceased, transfer those subscriptions instead
                $deathPaymentId = $deathPaymentCheck ? (int) $deathPaymentCheck['id'] : null;
                $this->createNewSubscriptions($db, $newMemberId, $caseId, $deathPaymentId, (int) $case['member_id']);

                ArchiveService::recordNumberTransfer($inheritedNumber, 'death_transfer', 'members', $newMemberId);

                $auditLog[] = 'تاريخ التنفيذ: ' . date('Y-m-d H:i:s');
                $auditLog[] = 'بواسطة: ' . ($employee ? $employee->full_name_ar : 'النظام');
                $auditLog[] = 'تم إنشاء العضوية الجديدة بعد اعتماد السداد من الخزينة';

                DeathAuditService::log($caseId, 'transfer_completed', 'تم إتمام نقل العضوية بالكامل — رقم العضوية: ' . $inheritedNumber, 'death_cases', $caseId);

                // Store audit log in notes (merge with existing)
                $finalNotes = !empty($case['notes']) ? json_decode($case['notes'], true) : [];
                if (!is_array($finalNotes)) $finalNotes = [];
                $finalNotes['audit_log'] = $auditLog;

                $db->update('death_cases', [
                    'transferred_to_member_id' => $newMemberId,
                    'archive_snapshot_id'      => $snapshotId,
                    'status'                   => 'completed',
                    'notes'                    => json_encode($finalNotes, JSON_UNESCAPED_UNICODE),
                    'updated_at'               => date('Y-m-d H:i:s'),
                ], '`id` = ?', [$caseId]);

                $db->commit();

                EventBus::dispatch('death.completed', ['case_id' => $caseId, 'new_member_id' => $newMemberId]);
                Logger::info("Death case #{$id} completed", ['new_member' => $newMemberId, 'audit' => $auditLog]);

                return $this->redirect("/death/{$id}")->withSuccess('تم نقل العضوية بنفس الرقم — رقم العضوية: ' . $inheritedNumber);
            }

            // Non-primary-member death
            $auditLog[] = 'تاريخ التنفيذ: ' . date('Y-m-d H:i:s');
            $auditLog[] = 'بواسطة: ' . ($employee ? $employee->full_name_ar : 'النظام');

            DeathAuditService::log($caseId, 'case_completed', 'تم إتمام حالة الوفاة', 'death_cases', $caseId);

            $finalNotes = !empty($case['notes']) ? json_decode($case['notes'], true) : [];
            if (!is_array($finalNotes)) $finalNotes = [];
            $finalNotes['audit_log'] = $auditLog;

            $db->update('death_cases', [
                'archive_snapshot_id' => $snapshotId,
                'status'              => 'completed',
                'notes'               => json_encode($finalNotes, JSON_UNESCAPED_UNICODE),
                'updated_at'          => date('Y-m-d H:i:s'),
            ], '`id` = ?', [$caseId]);

            $db->commit();
            return $this->redirect("/death/{$id}")->withSuccess('تم إتمام حالة الوفاة');
        } catch (\Throwable $e) {
            $db->rollBack();
            Logger::error("Death case #{$id} failed: " . $e->getMessage());
            return $this->redirect("/death/{$id}")->withError('فشل: ' . $e->getMessage());
        }
    }

    private function getCurrentPlanPrice($db, array $member): string
    {
        $branchId = (int) ($member['branch_id'] ?? 1);
        $qualId = !empty($member['qualification_id']) ? (int) $member['qualification_id'] : null;
        $currentPlanPrice = '0.00';

        if ($qualId) {
            $qual = $db->selectOne("SELECT code FROM qualifications WHERE id = ?", [$qualId]);
            if ($qual && $qual['code']) {
                $priceInfo = \App\Modules\Pricing\Services\PricingEngine::getMembershipPrice($branchId, $qual['code']);
                if (!empty($priceInfo['price']) && bccomp($priceInfo['price'], '0.01', 2) >= 0) {
                    $currentPlanPrice = $priceInfo['price'];
                }
            }
        }
        if (bccomp($currentPlanPrice, '0.01', 2) < 0) {
            $pr = $db->selectOne(
                "SELECT price FROM pricing_configs WHERE branch_id = ? AND is_active = 1 AND effective_from <= CURDATE() AND (effective_to IS NULL OR effective_to >= CURDATE()) ORDER BY price DESC LIMIT 1",
                [$branchId]
            );
            if ($pr) $currentPlanPrice = $pr['price'];
        }
        if (bccomp($currentPlanPrice, '0.01', 2) < 0) {
            $pr = $db->selectOne("SELECT price FROM pricing_configs WHERE is_active = 1 ORDER BY price DESC LIMIT 1");
            if ($pr) $currentPlanPrice = $pr['price'];
        }
        if (bccomp($currentPlanPrice, '0.01', 2) < 0) {
            $currentPlanPrice = $member['membership_value'] ?? '0.00';
        }

        return $currentPlanPrice;
    }

    private function transferActiveData($db, int $deceasedMemberId, int $newMemberId, int $caseId): void
    {
        // Transfer active/unpaid installment plans (schedule + cheques follow via FK)
        $installments = $db->select(
            "SELECT id FROM installment_plans WHERE member_id = ? AND status IN ('active', 'partial')",
            [$deceasedMemberId]
        );
        if (!empty($installments)) {
            $ids = array_column($installments, 'id');
            $placeholders = implode(',', array_fill(0, count($ids), '?'));
            $db->query(
                "UPDATE installment_plans SET member_id = ?, updated_at = NOW() WHERE id IN ({$placeholders})",
                array_merge([$newMemberId], $ids)
            );
            DeathAuditService::log($caseId, 'installments_transferred', 'تم نقل ' . count($installments) . ' خطة أقساط نشطة إلى العضوية الجديدة', 'members', $newMemberId);
        }

        // Transfer documents (duplicate record pointing to same file, keep originals with archived member)
        $docs = $db->select(
            "SELECT * FROM documents WHERE member_id = ? AND is_archived = 0",
            [$deceasedMemberId]
        );
        if (!empty($docs)) {
            foreach ($docs as $doc) {
                $db->insert('documents', [
                    'member_id'           => $newMemberId,
                    'document_type'       => $doc['document_type'],
                    'original_filename'   => $doc['original_filename'],
                    'stored_filename'     => $doc['stored_filename'],
                    'file_path'           => $doc['file_path'],
                    'file_size'           => (int) $doc['file_size'],
                    'mime_type'           => $doc['mime_type'],
                    'related_entity_type' => $doc['related_entity_type'],
                    'related_entity_id'   => $doc['related_entity_id'] ? (int) $doc['related_entity_id'] : null,
                    'description'         => ($doc['description'] ?? '') . ' (منقول من العضوية الأصلية #' . $deceasedMemberId . ')',
                    'uploaded_by'         => $doc['uploaded_by'] ? (int) $doc['uploaded_by'] : null,
                    'uploaded_at'         => $doc['uploaded_at'],
                    'created_at'          => date('Y-m-d H:i:s'),
                    'updated_at'          => date('Y-m-d H:i:s'),
                ]);
            }
            DeathAuditService::log($caseId, 'documents_linked', 'تم نقل ' . count($docs) . ' مستند إلى العضوية الجديدة', 'members', $newMemberId);
        }
    }

    private function createNewSubscriptions($db, int $newMemberId, int $caseId, ?int $paymentId = null, int $deceasedMemberId = 0): void
    {
        $now = date('Y-m-d H:i:s');
        $currentMonth = (int) date('n');
        $currentYear = (int) date('Y');
        $financialYear = $currentMonth >= 7
            ? $currentYear . '/' . ($currentYear + 1)
            : ($currentYear - 1) . '/' . $currentYear;

        $memberSub = ServicePrice::getPrice('SVC_ANNUAL_MEMBER', '492.00');
        $spouseSub = ServicePrice::getPrice('SVC_ANNUAL_SPOUSE', '492.00');
        $childSub  = ServicePrice::getPrice('SVC_ANNUAL_CHILD', '222.00');
        $tempSub   = ServicePrice::getPrice('SVC_ANNUAL_TEMP', '222.00');
        $devFeeData = RuleEngine::get('DEVELOPMENT_FEE');
        $devFee = $devFeeData['amount'] ?? '35.00';

        $employee = App::getInstance()->currentEmployee();
        $createdBy = $employee ? (int) $employee->id : null;
        $paidBy = $employee ? (int) $employee->id : null;

        $memberTotal = bcadd($memberSub, $devFee, 2);

        // If deceased already had current FY subscriptions, transfer them to new member
        if ($deceasedMemberId > 0) {
            $deceasedHasCurrentFY = $db->selectOne(
                "SELECT id FROM subscriptions WHERE member_id = ? AND financial_year = ? AND person_type = 'member' LIMIT 1",
                [$deceasedMemberId, $financialYear]
            );
            if ($deceasedHasCurrentFY) {
                // Transfer all current FY subscriptions from deceased to new member
                $db->query(
                    "UPDATE subscriptions SET member_id = ?, person_id = CASE WHEN person_type = 'member' THEN ? ELSE person_id END, updated_at = ? WHERE member_id = ? AND financial_year = ?",
                    [$newMemberId, $newMemberId, $now, $deceasedMemberId, $financialYear]
                );
                // Remove the transferred primary-spouse row (she's now the member, not a spouse)
                $db->query(
                    "DELETE FROM subscriptions WHERE member_id = ? AND financial_year = ? AND person_type = 'spouse' AND person_id IN (SELECT id FROM spouses WHERE member_id = ? AND status = 'transferred')",
                    [$newMemberId, $financialYear, $newMemberId]
                );
                DeathAuditService::log($caseId, 'subscriptions_transferred', 'تم نقل اشتراكات السنة المالية الحالية ' . $financialYear . ' من العضوية الأصلية (بدون رسوم إضافية — السنة مغطاة)', 'members', $newMemberId);
                return;
            }
        }

        // Member subscription (marked as paid — fee included in death transfer payment)
        $existing = $db->selectOne(
            "SELECT id, status FROM subscriptions WHERE member_id = ? AND financial_year = ? AND person_type = 'member'",
            [$newMemberId, $financialYear]
        );
        if ($existing && $existing['status'] === 'paid') {
            // Already paid — don't recreate
        } elseif ($existing) {
            // Exists but unpaid — mark as paid (fee was collected with death transfer)
            $db->update('subscriptions', [
                'paid_amount' => $memberTotal, 'status' => 'paid', 'paid_at' => $now,
                'payment_id' => $paymentId, 'paid_by' => $paidBy, 'updated_at' => $now,
            ], '`id` = ?', [(int) $existing['id']]);
        } else {
            $db->insert('subscriptions', [
                'member_id'       => $newMemberId,
                'financial_year'  => $financialYear,
                'person_type'     => 'member',
                'person_id'       => $newMemberId,
                'base_amount'     => $memberSub,
                'development_fee' => $devFee,
                'discount_amount' => '0.00',
                'total_amount'    => $memberTotal,
                'paid_amount'     => $memberTotal,
                'fine_amount'     => '0.00',
                'status'          => 'paid',
                'paid_at'         => $now,
                'payment_id'      => $paymentId,
                'paid_by'         => $paidBy,
                'created_at'      => $now,
                'updated_at'      => $now,
                'created_by'      => $createdBy,
            ]);
        }

        // Spouse subscriptions (marked as paid — dedup-safe)
        $spouses = $db->select("SELECT id, full_name_ar FROM spouses WHERE member_id = ? AND is_archived = 0 AND status = 'active'", [$newMemberId]);
        foreach ($spouses as $s) {
            $exSp = $db->selectOne(
                "SELECT id, status FROM subscriptions WHERE member_id = ? AND financial_year = ? AND person_type = 'spouse' AND person_id = ?",
                [$newMemberId, $financialYear, (int) $s['id']]
            );
            if ($exSp && $exSp['status'] === 'paid') { continue; }
            if ($exSp) {
                $db->update('subscriptions', ['paid_amount' => $spouseSub, 'status' => 'paid', 'paid_at' => $now, 'payment_id' => $paymentId, 'paid_by' => $paidBy, 'updated_at' => $now], '`id` = ?', [(int) $exSp['id']]);
            } else {
                $db->insert('subscriptions', [
                    'member_id' => $newMemberId, 'financial_year' => $financialYear, 'person_type' => 'spouse',
                    'person_id' => (int) $s['id'], 'person_name' => $s['full_name_ar'], 'base_amount' => $spouseSub,
                    'development_fee' => '0.00', 'discount_amount' => '0.00', 'total_amount' => $spouseSub,
                    'paid_amount' => $spouseSub, 'fine_amount' => '0.00', 'status' => 'paid',
                    'paid_at' => $now, 'payment_id' => $paymentId, 'paid_by' => $paidBy,
                    'created_at' => $now, 'updated_at' => $now, 'created_by' => $createdBy,
                ]);
            }
        }

        // Child subscriptions (marked as paid — dedup-safe)
        $children = $db->select("SELECT id, full_name_ar FROM children WHERE member_id = ? AND is_archived = 0 AND status = 'active'", [$newMemberId]);
        foreach ($children as $c) {
            $exCh = $db->selectOne(
                "SELECT id, status FROM subscriptions WHERE member_id = ? AND financial_year = ? AND person_type = 'child' AND person_id = ?",
                [$newMemberId, $financialYear, (int) $c['id']]
            );
            if ($exCh && $exCh['status'] === 'paid') { continue; }
            if ($exCh) {
                $db->update('subscriptions', ['paid_amount' => $childSub, 'status' => 'paid', 'paid_at' => $now, 'payment_id' => $paymentId, 'paid_by' => $paidBy, 'updated_at' => $now], '`id` = ?', [(int) $exCh['id']]);
            } else {
                $db->insert('subscriptions', [
                    'member_id' => $newMemberId, 'financial_year' => $financialYear, 'person_type' => 'child',
                    'person_id' => (int) $c['id'], 'person_name' => $c['full_name_ar'], 'base_amount' => $childSub,
                    'development_fee' => '0.00', 'discount_amount' => '0.00', 'total_amount' => $childSub,
                    'paid_amount' => $childSub, 'fine_amount' => '0.00', 'status' => 'paid',
                    'paid_at' => $now, 'payment_id' => $paymentId, 'paid_by' => $paidBy,
                    'created_at' => $now, 'updated_at' => $now, 'created_by' => $createdBy,
                ]);
            }
        }

        // Temp subscriptions (marked as paid — dedup-safe)
        $temps = $db->select("SELECT id, full_name_ar FROM temporary_members WHERE member_id = ? AND is_archived = 0 AND status = 'active'", [$newMemberId]);
        foreach ($temps as $t) {
            $exTmp = $db->selectOne(
                "SELECT id, status FROM subscriptions WHERE member_id = ? AND financial_year = ? AND person_type = 'temporary' AND person_id = ?",
                [$newMemberId, $financialYear, (int) $t['id']]
            );
            if ($exTmp && $exTmp['status'] === 'paid') { continue; }
            if ($exTmp) {
                $db->update('subscriptions', ['paid_amount' => $tempSub, 'status' => 'paid', 'paid_at' => $now, 'payment_id' => $paymentId, 'paid_by' => $paidBy, 'updated_at' => $now], '`id` = ?', [(int) $exTmp['id']]);
            } else {
                $db->insert('subscriptions', [
                    'member_id' => $newMemberId, 'financial_year' => $financialYear, 'person_type' => 'temporary',
                    'person_id' => (int) $t['id'], 'person_name' => $t['full_name_ar'], 'base_amount' => $tempSub,
                    'development_fee' => '0.00', 'discount_amount' => '0.00', 'total_amount' => $tempSub,
                    'paid_amount' => $tempSub, 'fine_amount' => '0.00', 'status' => 'paid',
                    'paid_at' => $now, 'payment_id' => $paymentId, 'paid_by' => $paidBy,
                    'created_at' => $now, 'updated_at' => $now, 'created_by' => $createdBy,
                ]);
            }
        }

        $totalSubs = 1 + count($spouses) + count($children) + count($temps);
        DeathAuditService::log($caseId, 'subscriptions_created', 'تم إنشاء ' . $totalSubs . ' اشتراك سنوي للسنة المالية ' . $financialYear, 'members', $newMemberId, [
            'financial_year' => $financialYear,
            'member' => 1,
            'spouses' => count($spouses),
            'children' => count($children),
            'temps' => count($temps),
        ]);
    }
}
