<?php
declare(strict_types=1);

namespace App\Modules\Members\Controllers;

use App\Core\Controller;
use App\Core\Request;
use App\Core\Response;
use App\Core\App;
use App\Core\EventBus;
use App\Modules\Members\Models\Member;
use App\Modules\Members\Services\NationalIdParser;
use App\Modules\Members\Services\MemberNumberGenerator;
use App\Modules\Members\Services\MemberSearchService;
use App\Modules\Members\Services\BillingService;
use App\Modules\Payments\Services\PaymentService;
use App\Modules\Cashier\Services\PaymentRequestService;
use App\Modules\Rules\Services\RuleEngine;
use App\Modules\Pricing\Models\SpecialDiscount;
use App\Modules\Workflow\Services\WorkflowEngine;
use App\Modules\Forms\Services\FormBridge;
use App\Core\Logger;
use App\Shared\Services\PhotoUploadService;
use App\Modules\Members\Services\MembershipRulesService;
use App\Modules\Members\Services\BoardOfferService;

class MemberController extends Controller
{
    private static function isSuperAdmin(): bool
    {
        $employee = App::getInstance()->currentEmployee();
        if (!$employee) return false;
        $db = App::getInstance()->db();
        $row = $db->selectOne(
            "SELECT 1 FROM employee_roles er JOIN roles r ON r.id = er.role_id WHERE er.employee_id = ? AND r.role_code = 'super_admin' AND er.is_active = 1 LIMIT 1",
            [(int) $employee->id]
        );
        return $row !== null;
    }

    public function index(Request $request): Response
    {
        $db = App::getInstance()->db();
        $filters = [
            'q' => trim((string) $request->get('q', '')),
            'status' => $request->get('status', ''),
            'branch_id' => $request->get('branch_id', ''),
            'date_from' => $request->get('date_from', ''),
            'date_to' => $request->get('date_to', ''),
        ];
        $page = max(1, (int) $request->get('page', 1));
        $perPage = 25;
        $where = 'm.is_archived = 0';
        $params = [];
        if ($filters['q'] !== '') { $where .= ' AND (m.full_name_ar LIKE ? OR m.national_id LIKE ? OR m.membership_number LIKE ? OR m.phone_mobile LIKE ? OR m.form_number LIKE ?)'; $s = '%' . $filters['q'] . '%'; $params = array_merge($params, [$s, $s, $s, $s, $s]); }
        if ($filters['status'] !== '') { $where .= ' AND m.status = ?'; $params[] = $filters['status']; }
        if ($filters['branch_id'] !== '') { $where .= ' AND m.branch_id = ?'; $params[] = (int) $filters['branch_id']; }
        if ($filters['date_from'] !== '') { $where .= ' AND m.created_at >= ?'; $params[] = $filters['date_from'] . ' 00:00:00'; }
        if ($filters['date_to'] !== '') { $where .= ' AND m.created_at <= ?'; $params[] = $filters['date_to'] . ' 23:59:59'; }
        $countRow = $db->selectOne("SELECT COUNT(*) as cnt FROM members m WHERE {$where}", $params);
        $total = (int) ($countRow['cnt'] ?? 0);
        $offset = ($page - 1) * $perPage;
        $members = $db->select("SELECT m.*, b.name_ar as branch_name FROM members m LEFT JOIN branches b ON b.id = m.branch_id WHERE {$where} ORDER BY m.id DESC LIMIT {$perPage} OFFSET {$offset}", $params);
        $pagination = \App\Core\Pagination::paginate($total, $perPage, $page);
        $branches = $db->select("SELECT id, name_ar FROM branches WHERE is_active = 1 ORDER BY name_ar");
        return $this->view('Members.Views.index', ['members' => $members, 'branches' => $branches, 'statuses' => Member::getStatusOptions(), 'filters' => $filters, 'pagination' => $pagination]);
    }

    public function create(Request $request): Response
    {
        $db = App::getInstance()->db();
        $branches = $db->select("SELECT id, name_ar FROM branches WHERE is_active = 1 ORDER BY name_ar");
        $qualifications = $db->select("SELECT id, name_ar FROM qualifications WHERE is_active = 1 ORDER BY sort_order");
        return $this->view('Members.Views.create', [
            'branches' => $branches,
            'qualifications' => $qualifications,
            'nextFormNumber' => MemberNumberGenerator::next(),
            'formFee' => MemberNumberGenerator::getFormFee(),
            'needsStartNumber' => (MemberNumberGenerator::next() === null),
            'membershipTypes' => MembershipRulesService::getCreatableMembershipTypes(),
        ]);
    }

    public function store(Request $request): Response
    {
        $db = App::getInstance()->db();
        $startNumber = $request->post('form_start_number');
        if ($startNumber !== null && $startNumber !== '') { MemberNumberGenerator::setStartNumber((int) $startNumber); }

        $fullNameAr = trim((string) $request->post('full_name_ar', ''));
        $nationalId = trim((string) $request->post('national_id', ''));
        $phoneMobile = trim((string) $request->post('phone_mobile', ''));
        $branchId = (int) $request->post('branch_id', 0);
        $membershipType = trim((string) $request->post('membership_type', 'working'));
        $validTypes = array_keys(MembershipRulesService::getCreatableMembershipTypes());
        if (!in_array($membershipType, $validTypes, true)) {
            $membershipType = 'working';
        }
        $errors = [];
        if ($fullNameAr === '' || mb_strlen($fullNameAr) < 5) $errors[] = 'الاسم بالعربي مطلوب (5 أحرف على الأقل)';
        if ($phoneMobile === '' || !preg_match('/^01[0125]\d{8}$/', $phoneMobile)) $errors[] = 'رقم المحمول غير صالح';
        if ($branchId <= 0) $errors[] = 'الفرع مطلوب';
        if ($membershipType === 'sports' && empty($request->post('qualification_id'))) $errors[] = 'المؤهل مطلوب للعضوية الرياضية';

        $dob = $gender = $govCode = null; $ageYears = $ageMonths = null; $idType = 'national_id';
        if ($nationalId !== '') {
            $parsed = NationalIdParser::parse($nationalId);
            if (!$parsed['is_valid']) { $errors = array_merge($errors, $parsed['errors']); }
            else { $dob = $parsed['dob']; $ageYears = $parsed['age_years']; $ageMonths = $parsed['age_months']; $gender = $parsed['gender']; $govCode = $parsed['governorate_code']; }
            $dup = $db->selectOne("SELECT id, full_name_ar, is_archived FROM members WHERE national_id = ?", [$nationalId]);
            if ($dup) {
                if ($dup['is_archived']) {
                    $errors[] = 'الرقم القومي مسجل في عضوية مؤرشفة: ' . $dup['full_name_ar'];
                } else {
                    $errors[] = 'الرقم القومي مسجل بالفعل: ' . $dup['full_name_ar'];
                }
            }
        } else {
            $dob = $request->post('date_of_birth_manual', '') ?: $request->post('date_of_birth', '');
            $gender = $request->post('gender_manual', '') ?: $request->post('gender', '');
            if (!$dob) $errors[] = 'تاريخ الميلاد مطلوب'; if (!$gender) $errors[] = 'النوع مطلوب';
            if ($dob) { $age = age_from_dob($dob); $ageYears = $age['years']; $ageMonths = $age['months']; }
            $idType = 'passport';
        }
        if ($gender !== null && !in_array($gender, ['male', 'female'], true)) {
            $errors[] = 'النوع يجب أن يكون ذكر أو أنثى';
        }
        $typeMinAge = MembershipRulesService::getMinAgeForType($membershipType);
        if ($typeMinAge > 0 && $ageYears !== null && $ageYears < $typeMinAge) {
            $typeLabel = MembershipRulesService::getMembershipTypeLabel($membershipType);
            $errors[] = 'الحد الأدنى لسن ' . $typeLabel . ' ' . $typeMinAge . ' سنة (السن الحالي: ' . $ageYears . ')';
        }

        $photoFile = $_FILES['photo'] ?? [];
        if (!PhotoUploadService::isUploaded($photoFile)) {
            $errors[] = 'الصورة الشخصية مطلوبة';
        } else {
            $photoValidation = PhotoUploadService::validate($photoFile);
            if (!$photoValidation['valid']) {
                $errors = array_merge($errors, $photoValidation['errors']);
            }
        }

        if (!empty($errors)) { $session = App::getInstance()->session(); $session->flash('_alerts', array_map(fn($e) => ['type' => 'error', 'message' => $e], $errors)); $session->flash('_old_input', $request->all()); return $this->redirect('/members/create'); }

        $formNumber = MemberNumberGenerator::next();
        if (!$formNumber) return $this->redirect('/members/create')->withError('يجب تحديد رقم بداية الاستمارات');

        $nationality = ($idType === 'passport') ? 'أجنبي' : 'مصري';
        if ($membershipType === 'foreign') {
            $nationality = 'أجنبي';
        }
        $qualificationId = (int) $request->post('qualification_id', 0) ?: null;
        $member = Member::create(['full_name_ar' => $fullNameAr, 'national_id' => $nationalId ?: null, 'passport_number' => $request->post('passport_number') ?: null, 'id_type' => $idType, 'date_of_birth' => $dob, 'age_years' => $ageYears, 'age_months' => $ageMonths, 'gender' => $gender, 'governorate_code' => $govCode, 'phone_mobile' => $phoneMobile, 'branch_id' => $branchId, 'nationality' => $nationality, 'form_number' => (string) $formNumber, 'form_date' => date('Y-m-d'), 'status' => 'potential', 'membership_type' => $membershipType, 'member_category' => $membershipType . '_member', 'qualification_id' => $qualificationId]);
        $photoResult = PhotoUploadService::upload($photoFile, 'members', (int) $member->id);
        if ($photoResult) {
            $member->update(['photo_path' => $photoResult['path']]);
        }

        if (WorkflowEngine::hasDefinition('new_membership')) {
            try {
                $wfInstance = WorkflowEngine::createInstance('new_membership', 'members', (int) $member->id);
                $member->update(['workflow_instance_id' => (int) $wfInstance->id]);
            } catch (\Throwable $e) {
                Logger::warning("Workflow instance creation failed for new member", ['member_id' => (int) $member->id, 'error' => $e->getMessage()]);
            }
        }
        EventBus::dispatch('member.created', ['member_id' => (int) $member->id, 'form_number' => (string) $formNumber]);

        $typeRedirects = [
            'foreign'  => '/members/' . $member->id . '/foreign/create',
            'honorary' => '/members/' . $member->id . '/honorary/create',
            'sports'   => '/members/' . $member->id . '/sports/create',
            'seasonal' => '/members/' . $member->id . '/seasonal/create',
        ];
        $redirectUrl = $typeRedirects[$membershipType] ?? '/members/' . $member->id;
        return $this->redirect($redirectUrl)->withSuccess('تم تسجيل العضو — استمارة رقم: ' . $formNumber);
    }

    public function show(Request $request, string $id): Response
    {
        $db = App::getInstance()->db();
        $member = Member::find((int) $id);
        if (!$member) return $this->redirect('/members')->withError('العضو غير موجود');
        if ($member->is_archived) return $this->redirect('/members/archive/' . (int) $id);

        $branch = $db->selectOne("SELECT name_ar FROM branches WHERE id = ?", [(int) $member->branch_id]);
        $qualification = $member->qualification_id ? $db->selectOne("SELECT name_ar FROM qualifications WHERE id = ?", [(int) $member->qualification_id]) : null;

        $spouses = []; $children = []; $temporaries = [];
        try {
            $spouses = $db->select(
                "SELECT s.*, pr.created_at AS due_date, p.payment_date
                 FROM spouses s
                 LEFT JOIN payment_requests pr ON pr.related_entity_type = 'spouses' AND pr.related_entity_id = s.id AND pr.payment_type = 'addition_fee' AND pr.is_voided = 0
                 LEFT JOIN payments p ON p.id = s.activated_by_payment_id AND p.is_voided = 0
                 WHERE s.member_id = ? AND s.is_archived = 0
                 ORDER BY s.spouse_order", [(int) $id]
            );
        } catch (\Throwable $e) {
            try { $spouses = $db->select("SELECT * FROM spouses WHERE member_id = ? AND is_archived = 0 ORDER BY spouse_order", [(int) $id]); } catch (\Throwable $e2) {}
        }
        try {
            $children = $db->select(
                "SELECT c.*, TIMESTAMPDIFF(YEAR, c.date_of_birth, CURDATE()) as age_years, TIMESTAMPDIFF(MONTH, c.date_of_birth, CURDATE()) % 12 as age_months,
                        pr.created_at AS due_date, p.payment_date,
                        tr.target_member_id as separated_member_id, tm.membership_number as separated_membership_number
                 FROM children c
                 LEFT JOIN payment_requests pr ON pr.related_entity_type = 'children' AND pr.related_entity_id = c.id AND pr.payment_type = 'addition_fee' AND pr.is_voided = 0
                 LEFT JOIN payments p ON p.id = c.activated_by_payment_id AND p.is_voided = 0
                 LEFT JOIN transfer_requests tr ON tr.child_id = c.id AND tr.status = 'completed'
                 LEFT JOIN members tm ON tm.id = tr.target_member_id
                 WHERE c.member_id = ?
                 ORDER BY c.is_archived ASC, c.child_order", [(int) $id]
            );
        } catch (\Throwable $e) {
            try { $children = $db->select("SELECT *, TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) as age_years, TIMESTAMPDIFF(MONTH, date_of_birth, CURDATE()) % 12 as age_months FROM children WHERE member_id = ? ORDER BY is_archived ASC, child_order", [(int) $id]); } catch (\Throwable $e2) {}
        }
        try {
            $temporaries = $db->select(
                "SELECT t.*, pr.created_at AS due_date, p.payment_date
                 FROM temporary_members t
                 LEFT JOIN payment_requests pr ON pr.related_entity_type = 'temporary_members' AND pr.related_entity_id = t.id AND pr.payment_type = 'addition_fee' AND pr.is_voided = 0
                 LEFT JOIN payments p ON p.id = t.activated_by_payment_id AND p.is_voided = 0
                 WHERE t.member_id = ? AND t.is_archived = 0
                 ORDER BY t.id", [(int) $id]
            );
        } catch (\Throwable $e) {
            try { $temporaries = $db->select("SELECT * FROM temporary_members WHERE member_id = ? AND is_archived = 0 ORDER BY id", [(int) $id]); } catch (\Throwable $e2) {}
        }

        // Reconcile membership status against payment source-of-truth
        \App\Modules\Members\Services\MembershipPaymentGuard::reconcile((int) $id);

        $bill = BillingService::getMemberBill((int) $id);
        $membershipType = $member->membership_type ?? 'working';
        $formFilled = ($membershipType !== 'working')
            ? true
            : ($member->qualification_id !== null && $member->qualification_id > 0);

        $specialDiscount = null;
        if ($member->special_discount_id) {
            $specialDiscount = $db->selectOne("SELECT * FROM special_discounts WHERE id = ?", [(int) $member->special_discount_id]);
        }

        $pendingRequests = PaymentRequestService::getForMember((int) $id);
        $pendingFormFee = null;
        $pendingMembership = null;
        $pendingAdditions = [];
        $cancelledRequests = [];
        foreach ($pendingRequests as $pr) {
            if ($pr['status'] === 'pending' || $pr['status'] === 'processing') {
                if ($pr['payment_type'] === 'form_fee') $pendingFormFee = $pr;
                if (in_array($pr['payment_type'], ['membership_fee', 'down_payment', 'foreign_membership_fee', 'sports_membership_fee', 'seasonal_fee'], true)) $pendingMembership = $pr;
                if ($pr['payment_type'] === 'addition_fee' && $pr['related_entity_type'] && $pr['related_entity_id']) {
                    $pendingAdditions[$pr['related_entity_type'] . ':' . $pr['related_entity_id']] = $pr;
                }
            } elseif ($pr['status'] === 'cancelled' && empty($pr['is_voided'])) {
                $cancelledRequests[] = $pr;
            }
        }

        // Check if this member was created via divorce transfer
        $divorceTransfer = null;
        if ($member->transferred_from_divorce_id) {
            $divorceTransfer = $db->selectOne(
                "SELECT dc.id, dc.original_membership_number, m.full_name_ar as original_member_name, m.id as original_member_id
                 FROM divorce_cases dc LEFT JOIN members m ON m.id = dc.original_member_id
                 WHERE dc.id = ?",
                [(int) $member->transferred_from_divorce_id]
            );
        }

        // Check if this member was created via waiver transfer
        $waiverTransfer = null;
        $waiverArchiveSnapshot = null;
        if (!empty($member->transferred_from_waiver_id)) {
            $waiverTransfer = $db->selectOne(
                "SELECT wr.id, wr.source_member_id, wr.membership_number, wr.waiver_fee_amount, wr.waiver_fee_percentage,
                        wr.archive_snapshot_id, wr.created_at as waiver_date,
                        m.full_name_ar as source_member_name, m.id as source_member_id_ref
                 FROM waiver_requests wr LEFT JOIN members m ON m.id = wr.source_member_id
                 WHERE wr.id = ?",
                [(int) $member->transferred_from_waiver_id]
            );
            if ($waiverTransfer && $waiverTransfer['archive_snapshot_id']) {
                $waiverArchiveSnapshot = \App\Modules\Archive\Services\ArchiveService::getSnapshot((int) $waiverTransfer['archive_snapshot_id']);
            }
        }

        // Check if this member was created via death transfer
        $deathTransfer = null;
        if (!empty($member->transferred_from_death_id)) {
            $deathTransfer = $db->selectOne(
                "SELECT dc.id, dc.member_id as deceased_member_id, m.full_name_ar as deceased_member_name,
                        dc.updated_at as transfer_date
                 FROM death_cases dc LEFT JOIN members m ON m.id = dc.member_id
                 WHERE dc.id = ?",
                [(int) $member->transferred_from_death_id]
            );
            if ($deathTransfer) {
                $deathPayment = $db->selectOne(
                    "SELECT amount, payment_date FROM payments WHERE member_id = ? AND payment_type = 'death_transfer_fee' AND is_voided = 0 ORDER BY id DESC LIMIT 1",
                    [(int) $id]
                );
                $deathTransfer['paid_amount'] = $deathPayment['amount'] ?? null;
                $deathTransfer['payment_date'] = $deathPayment['payment_date'] ?? null;
            }
        }

        // Subscription status for current FY
        $currentMonth = (int) date('n');
        $currentYear = (int) date('Y');
        $currentFY = $currentMonth >= 7 ? "$currentYear-" . ($currentYear + 1) : ($currentYear - 1) . "-$currentYear";
        $subscriptionStatus = $db->selectOne(
            "SELECT id, financial_year, status, total_amount, paid_amount, fine_amount
             FROM subscriptions WHERE member_id = ? AND financial_year = ? LIMIT 1",
            [(int) $id, $currentFY]
        );
        $overdueSubscriptions = $db->select(
            "SELECT financial_year, total_amount, paid_amount, fine_amount, status
             FROM subscriptions WHERE member_id = ? AND status IN ('pending','overdue')
             ORDER BY financial_year ASC",
            [(int) $id]
        );

        // Check if this member was created via child separation transfer
        $separationTransfer = null;
        if (!empty($member->transferred_from_transfer_id)) {
            $separationTransfer = $db->selectOne(
                "SELECT tr.id, tr.source_member_id, tr.total_fee, tr.separation_fee, tr.transfer_type,
                        tr.completed_at as transfer_date, tr.effective_transfer_date,
                        m.full_name_ar as source_member_name, m.membership_number as source_number
                 FROM transfer_requests tr
                 LEFT JOIN members m ON m.id = tr.source_member_id
                 WHERE tr.id = ?",
                [(int) $member->transferred_from_transfer_id]
            );
        }

        // Transfer fee payment (for separated children / waiver-acquired members)
        $transferFeePayment = null;
        if (!empty($member->transferred_from_transfer_id) && $separationTransfer) {
            $transferFeePayment = $db->selectOne(
                "SELECT amount, payment_date FROM payments WHERE member_id = ? AND payment_type = 'separation_fee' AND related_entity_type = 'transfer_requests' AND related_entity_id = ? AND is_voided = 0 ORDER BY id DESC LIMIT 1",
                [(int) $separationTransfer['source_member_id'], (int) $member->transferred_from_transfer_id]
            );
        } elseif (!empty($member->transferred_from_waiver_id)) {
            $transferFeePayment = $db->selectOne(
                "SELECT amount, payment_date FROM payments WHERE member_id = ? AND payment_type = 'waiver_fee' AND is_voided = 0 ORDER BY id DESC LIMIT 1",
                [(int) $id]
            );
        } elseif (!empty($member->transferred_from_divorce_id)) {
            $transferFeePayment = $db->selectOne(
                "SELECT amount, payment_date FROM payments WHERE member_id = ? AND payment_type IN ('separation_fee','transfer_fee','child_separation_fee') AND is_voided = 0 ORDER BY id DESC LIMIT 1",
                [(int) $id]
            );
        }

        $instRateData    = RuleEngine::get('INSTALLMENT_INTEREST_RATE');
        $instMonthsData  = RuleEngine::get('INSTALLMENT_MAX_MONTHS');

        $boardOffers = BoardOfferService::getActiveOffers((int) ($member->branch_id ?? 0) ?: null);
        $bestOffer = $boardOffers[0] ?? null;
        $cashDiscount = $bestOffer ? BoardOfferService::getCashDiscount($bill['total_pending'], $bestOffer) : null;
        $installmentTerms = BoardOfferService::getInstallmentTerms($bestOffer);

        return $this->view('Members.Views.show', [
            'member' => $member,
            'branchName' => $branch['name_ar'] ?? '—',
            'qualificationName' => $qualification['name_ar'] ?? '—',
            'spouses' => $spouses,
            'children' => $children,
            'temporaries' => $temporaries,
            'bill' => $bill,
            'formFee' => MemberNumberGenerator::getFormFee(),
            'formFilled' => $formFilled,
            'specialDiscount' => $specialDiscount,
            'availableDiscounts' => SpecialDiscount::allActive(),
            'pendingFormFee' => $pendingFormFee,
            'pendingMembership' => $pendingMembership,
            'pendingAdditions' => $pendingAdditions,
            'cancelledRequests' => $cancelledRequests,
            'isSuperAdmin' => self::isSuperAdmin(),
            'divorceTransfer' => $divorceTransfer,
            'deathTransfer' => $deathTransfer,
            'waiverTransfer' => $waiverTransfer,
            'waiverArchiveSnapshot' => $waiverArchiveSnapshot,
            'installmentPlan' => $member->status === 'pending_cheques'
                ? $db->selectOne("SELECT id, number_of_months FROM installment_plans WHERE member_id = ? AND status = 'active' ORDER BY id DESC LIMIT 1", [(int) $id])
                : null,
            'installInterestRate' => (float) ($instRateData['percentage'] ?? 22),
            'installMaxMonths'    => (int)   ($instMonthsData['months']   ?? 30),
            'boardOffers'         => $boardOffers,
            'bestOffer'           => $bestOffer,
            'cashDiscount'        => $cashDiscount,
            'installmentTerms'    => $installmentTerms,
            'subscriptionStatus'  => $subscriptionStatus,
            'overdueSubscriptions' => $overdueSubscriptions,
            'transferFeePayment'  => $transferFeePayment,
            'separationTransfer'  => $separationTransfer,
        ]);
    }

    public function payFormFee(Request $request, string $id): Response
    {
        $member = Member::find((int) $id);
        if (!$member) return $this->redirect('/members')->withError('العضو غير موجود');

        $formFeeAmount = MemberNumberGenerator::getFormFee();
        $breakdown = [
            '📋 رسوم استمارة عضوية جديدة',
            '💰 رسوم الاستمارة: 500.00 ج.م',
            '🏛️ طابع شهداء: 5.00 ج.م',
            '═══════════════════════════',
            '💵 الإجمالي: ' . money($formFeeAmount),
        ];

        $result = PaymentRequestService::createRequest([
            'member_id'           => (int) $id,
            'amount'              => $formFeeAmount,
            'payment_type'        => 'form_fee',
            'related_entity_type' => 'members',
            'related_entity_id'   => (int) $id,
            'description_ar'      => 'رسوم استمارة عضوية رقم ' . ($member->form_number ?? ''),
            'notes'               => json_encode(['fee_breakdown' => $breakdown], JSON_UNESCAPED_UNICODE),
        ]);
        if (!$result['success']) return $this->redirect('/members/' . $id)->withError($result['error']);
        return $this->redirect('/members/' . $id)->withSuccess('تم إرسال طلب الدفع للخزينة — رقم الطلب: ' . $result['request_number']);
    }

    public function applyDiscount(Request $request, string $id): Response
    {
        $db = App::getInstance()->db();
        $member = Member::find((int) $id);
        if (!$member) return $this->redirect('/members')->withError('العضو غير موجود');

        $discountId = (int) $request->post('special_discount_id', 0);
        if ($discountId <= 0) {
            $db->update('members', [
                'special_discount_id' => null,
                'discount_amount'     => null,
                'updated_at'          => date('Y-m-d H:i:s'),
            ], '`id` = ?', [(int) $id]);
            return $this->redirect("/members/{$id}")->withSuccess('تم إزالة الخصم');
        }

        $discount = $db->selectOne("SELECT * FROM special_discounts WHERE id = ? AND is_active = 1", [$discountId]);
        if (!$discount) {
            return $this->redirect("/members/{$id}")->withError('الخصم غير موجود');
        }

        $membershipValue = $member->membership_value ?? '0.00';
        $discountAmount = bcdiv(bcmul($membershipValue, $discount['discount_percentage'], 4), '100', 2);

        $updateData = [
            'special_discount_id' => $discountId,
            'discount_amount'     => $discountAmount,
            'updated_at'          => date('Y-m-d H:i:s'),
        ];

        if ($discount['requires_document'] && !empty($_FILES['discount_document']['tmp_name'])) {
            $file = $_FILES['discount_document'];
            $ext = pathinfo($file['name'], PATHINFO_EXTENSION);
            $storedName = 'disc_' . $id . '_' . date('Ymd_His') . '.' . $ext;
            $destDir = __DIR__ . '/../../../../storage/uploads/discounts';
            if (!is_dir($destDir)) mkdir($destDir, 0755, true);
            move_uploaded_file($file['tmp_name'], $destDir . '/' . $storedName);
            $updateData['special_discount_document'] = 'storage/uploads/discounts/' . $storedName;
        }

        $db->update('members', $updateData, '`id` = ?', [(int) $id]);

        return $this->redirect("/members/{$id}")->withSuccess('تم تطبيق الخصم: ' . $discount['name_ar'] . ' (' . $discount['discount_percentage'] . '%)');
    }

    public function payMembership(Request $request, string $id): Response
    {
        $db = App::getInstance()->db();
        $member = Member::find((int) $id);
        if (!$member) return $this->redirect('/members')->withError('العضو غير موجود');

        $paymentType = trim((string) $request->post('payment_type', ''));
        $amount = trim((string) $request->post('amount', '0'));

        $validPaymentTypes = ['membership_fee', 'down_payment', 'foreign_membership_fee', 'sports_membership_fee', 'seasonal_fee'];
        if (!in_array($paymentType, $validPaymentTypes, true)) {
            return $this->redirect('/members/' . $id)->withError('نوع الدفع غير صالح');
        }

        if (bccomp($amount, '0.01', 2) < 0) return $this->redirect('/members/' . $id)->withError('المبلغ غير صالح');

        // Resolve active board offer (if any) at request time
        $offerId = (int) $request->post('board_offer_id', 0);
        $activeOffer = null;
        if ($offerId > 0) {
            $activeOffer = $db->selectOne("SELECT * FROM `board_offers` WHERE `id` = ? AND `is_active` = 1 AND `effective_from` <= ? AND `effective_to` >= ?", [$offerId, date('Y-m-d'), date('Y-m-d')]);
        }
        if (!$activeOffer) {
            $activeOffer = BoardOfferService::getBestOffer((int) ($member->branch_id ?? 0) ?: null);
        }
        $offerSnapshot = $activeOffer ? BoardOfferService::snapshotOffer($activeOffer) : null;

        $instTerms = BoardOfferService::getInstallmentTerms($activeOffer);
        $instMaxMonths = $instTerms['max_months'];
        $months = ($paymentType === 'down_payment') ? min($instMaxMonths, max(1, (int) $request->post('installment_months', $instMaxMonths))) : null;

        // Cancel any pending individual addition_fee requests — the collective payment subsumes them
        $pendingAdditions = $db->select(
            "SELECT id FROM payment_requests WHERE member_id = ? AND payment_type = 'addition_fee' AND status IN ('pending','processing') AND is_voided = 0",
            [(int) $id]
        );
        foreach ($pendingAdditions as $pa) {
            PaymentRequestService::cancelRequest((int) $pa['id'], 'ألغي تلقائياً — مشمول في الفاتورة المجمعة');
        }

        $bill = BillingService::getMemberBill((int) $id);
        $breakdown = [];
        foreach ($bill['items'] ?? [] as $item) {
            if ($item['paid'] || $item['included']) continue;
            $breakdown[] = ($item['category'] === 'discount' ? '🏷️ ' : '📌 ') . $item['label'] . ': ' . money($item['amount']);
        }
        $breakdown[] = '═══════════════════════════';
        if ($paymentType === 'down_payment' && $months) {
            $breakdown[] = '💵 مقدم التقسيط: ' . money($amount);
            $breakdown[] = '📅 عدد الأشهر: ' . $months;
            if ($activeOffer) {
                $breakdown[] = '🎯 عرض مجلس الإدارة: ' . ($activeOffer['title_ar'] ?? '');
            }
        } else {
            $breakdown[] = '💵 الإجمالي المطلوب: ' . money($amount);
            if ($activeOffer && $activeOffer['cash_discount_type']) {
                $cashInfo = BoardOfferService::getCashDiscount($bill['total_pending'], $activeOffer);
                if (bccomp($cashInfo['savings'], '0', 2) > 0) {
                    $breakdown[] = '🎯 خصم عرض مجلس الإدارة: ' . money($cashInfo['savings']);
                }
            }
        }

        $notesData = ['fee_breakdown' => $breakdown];
        if ($months) $notesData['installment_months'] = $months;

        $typeDescriptions = [
            'down_payment'             => 'مقدم تقسيط',
            'membership_fee'           => 'قيمة العضوية',
            'foreign_membership_fee'   => 'رسوم عضوية أجنبية',
            'sports_membership_fee'    => 'رسوم عضوية رياضية',
            'seasonal_fee'             => 'رسوم عضوية موسمية',
        ];
        $descriptionAr = ($typeDescriptions[$paymentType] ?? 'قيمة العضوية') . ' — استمارة ' . ($member->form_number ?? '');

        $result = PaymentRequestService::createRequest([
            'member_id'           => (int) $id,
            'amount'              => $amount,
            'payment_type'        => $paymentType,
            'related_entity_type' => 'members',
            'related_entity_id'   => (int) $id,
            'description_ar'      => $descriptionAr,
            'notes'               => json_encode($notesData, JSON_UNESCAPED_UNICODE),
            'board_offer_id'      => $activeOffer ? (int) $activeOffer['id'] : null,
            'offer_snapshot_json' => $offerSnapshot,
        ]);
        if (!$result['success']) return $this->redirect('/members/' . $id)->withError($result['error']);

        return $this->redirect('/members/' . $id)->withSuccess('تم إرسال طلب الدفع للخزينة — رقم الطلب: ' . $result['request_number']);
    }

    public function payAdditionFee(Request $request, string $id): Response
    {
        $db = App::getInstance()->db();
        $member = Member::find((int) $id);
        if (!$member) return $this->redirect('/members')->withError('العضو غير موجود');

        $entityType = trim((string) $request->post('entity_type', ''));
        $entityId = (int) $request->post('entity_id', 0);

        if (!in_array($entityType, ['spouses', 'children', 'temporary_members'], true) || $entityId <= 0) {
            return $this->redirect('/members/' . $id)->withError('بيانات غير صالحة');
        }

        // Block if a combined membership payment is already pending
        $hasCombined = $db->selectOne(
            "SELECT id FROM payment_requests WHERE member_id = ? AND payment_type IN ('membership_fee','down_payment','foreign_membership_fee','sports_membership_fee','seasonal_fee') AND status IN ('pending','processing') AND is_voided = 0 LIMIT 1",
            [(int) $id]
        );
        if ($hasCombined) {
            return $this->redirect('/members/' . $id)->withError('يوجد طلب دفع مجمع في الخزينة بالفعل — الرسوم مشمولة فيه');
        }

        $entity = $db->selectOne("SELECT * FROM {$entityType} WHERE id = ? AND member_id = ? AND is_archived = 0", [$entityId, (int) $id]);
        if (!$entity) return $this->redirect('/members/' . $id)->withError('العنصر غير موجود');

        $fee = $entity['addition_fee'] ?? '0.00';
        if (bccomp($fee, '0.01', 2) < 0) return $this->redirect('/members/' . $id)->withError('لا توجد رسوم مستحقة');

        // Check if already paid via payments table
        $alreadyPaid = $db->selectOne(
            "SELECT id FROM payments WHERE member_id = ? AND payment_type = 'addition_fee' AND related_entity_type = ? AND related_entity_id = ? AND is_voided = 0 LIMIT 1",
            [(int) $id, $entityType, $entityId]
        );
        if ($alreadyPaid) return $this->redirect('/members/' . $id)->withError('تم سداد رسوم هذا التابع بالفعل');

        $nameLabel = $entity['full_name_ar'] ?? '';
        $typeLabel = match ($entityType) {
            'spouses' => 'زوجة',
            'children' => ($entity['gender'] ?? '') === 'male' ? 'ابن' : 'ابنة',
            'temporary_members' => 'عضو مؤقت',
            default => '',
        };

        $breakdown = [
            '👤 ' . $typeLabel . ': ' . $nameLabel,
            '📌 نوع الرسوم: رسوم إضافة تابع',
            '💰 رسوم الإضافة: ' . money($fee),
            '═══════════════════════════',
            '💵 الإجمالي: ' . money($fee),
        ];

        $result = PaymentRequestService::createRequest([
            'member_id'           => (int) $id,
            'amount'              => $fee,
            'payment_type'        => 'addition_fee',
            'related_entity_type' => $entityType,
            'related_entity_id'   => $entityId,
            'description_ar'      => 'رسوم إضافة ' . $typeLabel . ' — ' . $nameLabel,
            'notes'               => json_encode(['fee_breakdown' => $breakdown], JSON_UNESCAPED_UNICODE),
        ]);

        if (!$result['success']) return $this->redirect('/members/' . $id)->withError($result['error']);

        return $this->redirect('/members/' . $id)->withSuccess('تم إرسال رسوم ' . $typeLabel . ' للخزينة — رقم الطلب: ' . $result['request_number']);
    }

    public function fillForm(Request $request, string $id): Response
    {
        $db = App::getInstance()->db();
        $member = Member::find((int) $id);
        if (!$member) return $this->redirect('/members')->withError('العضو غير موجود');
        if (!empty($member->membership_number) && !self::isSuperAdmin()) {
            return $this->redirect('/members/' . $id)->withError('لا يمكن تعديل الاستمارة بعد تفعيل العضوية — تواصل مع المشرف العام');
        }
        $formFeePaid = $db->selectOne("SELECT id FROM payments WHERE member_id = ? AND payment_type = 'form_fee' AND is_voided = 0 LIMIT 1", [(int) $id]);
        if (!$formFeePaid) return $this->redirect('/members/' . $id)->withError('⚠ يجب دفع رسوم الاستمارة أولاً');

        $salesReps = $db->select("SELECT id, code, name_ar FROM sales_representatives WHERE is_active = 1 ORDER BY name_ar", []);

        return $this->view('Members.Views.fill-form', [
            'member' => $member,
            'branches' => $db->select("SELECT id, name_ar FROM branches WHERE is_active = 1 ORDER BY name_ar"),
            '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"),
            'specialDiscounts' => SpecialDiscount::allActive(),
            'salesReps' => $salesReps,
            'schemaHtml' => '',
        ]);
    }

    public function saveFillForm(Request $request, string $id): Response
    {
        $db = App::getInstance()->db();
        $member = Member::find((int) $id);
        if (!$member) return $this->redirect('/members')->withError('العضو غير موجود');
        if (!empty($member->membership_number) && !self::isSuperAdmin()) {
            return $this->redirect('/members/' . $id)->withError('لا يمكن تعديل الاستمارة بعد تفعيل العضوية — تواصل مع المشرف العام');
        }
        $formFeePaid = $db->selectOne("SELECT id FROM payments WHERE member_id = ? AND payment_type = 'form_fee' AND is_voided = 0 LIMIT 1", [(int) $id]);
        if (!$formFeePaid) return $this->redirect('/members/' . $id)->withError('⚠ يجب دفع رسوم الاستمارة أولاً');

        $data = $request->all(); unset($data['_csrf_token']);
        $fields = ['full_name_en','place_of_birth','nationality','religion','qualification_id','marital_status','passport_number','id_issue_date','id_expiry_date','phone_home','phone_international','email','emergency_name','emergency_phone','residence_type','residence_address','landmark','floor','apartment','area','governorate','correspondence_address','employment_type','occupation','job_title','employment_date','business_address','office_phone','office_fax','business_activity','referral_source','referred_by_representative_id'];
        $update = [];
        foreach ($fields as $f) { if (array_key_exists($f, $data)) { $v = trim((string) ($data[$f] ?? '')); $update[$f] = $v === '' ? null : $v; } }
        if (isset($update['qualification_id'])) $update['qualification_id'] = $update['qualification_id'] ? (int) $update['qualification_id'] : null;
        if (isset($update['referred_by_representative_id'])) {
            $update['referred_by_representative_id'] = $update['referred_by_representative_id'] !== null && $update['referred_by_representative_id'] !== '' ? (int) $update['referred_by_representative_id'] : null;
        }

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

        // Special discount
        $discountId = trim((string) ($data['special_discount_id'] ?? ''));
        if ($discountId !== '' && $discountId !== '0') {
            $discountRow = $db->selectOne("SELECT * FROM special_discounts WHERE id = ? AND is_active = 1", [(int) $discountId]);
            if ($discountRow) {
                $update['special_discount_id'] = (int) $discountId;
                $mValue = $update['membership_value'] ?? ($member->membership_value ?? '0.00');
                $update['discount_amount'] = bcdiv(bcmul($mValue, $discountRow['discount_percentage'], 4), '100', 2);

                if ($discountRow['requires_document'] && !empty($_FILES['discount_document']['tmp_name'])) {
                    $file = $_FILES['discount_document'];
                    $allowedTypes = ['application/pdf', 'image/jpeg', 'image/png'];
                    $finfo = new \finfo(FILEINFO_MIME_TYPE);
                    $mimeType = $finfo->file($file['tmp_name']);
                    if (in_array($mimeType, $allowedTypes, true) && $file['size'] <= 10485760) {
                        $ext = pathinfo($file['name'], PATHINFO_EXTENSION);
                        $storedName = 'discount_' . (int) $id . '_' . date('Ymd_His') . '_' . bin2hex(random_bytes(4)) . '.' . $ext;
                        $uploadDir = App::getInstance()->basePath() . '/storage/uploads/discounts/';
                        if (!is_dir($uploadDir)) mkdir($uploadDir, 0755, true);
                        if (move_uploaded_file($file['tmp_name'], $uploadDir . $storedName)) {
                            $update['special_discount_document'] = 'storage/uploads/discounts/' . $storedName;
                        }
                    }
                }
            }
        }

        if ($member->status === 'potential') $update['status'] = 'under_review';
        if (!empty($update)) $member->update($update);

        if (FormBridge::exists('NEW_MEMBERSHIP')) {
            FormBridge::submit('NEW_MEMBERSHIP', $data, (int) $id, 'ملء استمارة عضوية');
        }

        return $this->redirect('/members/' . $id)->withSuccess('تم ملء الاستمارة');
    }

    public function edit(Request $request, string $id): Response
    {
        $db = App::getInstance()->db();
        $member = Member::find((int) $id);
        if (!$member) return $this->redirect('/members')->withError('العضو غير موجود');

        if (!empty($member->membership_number) && !self::isSuperAdmin()) {
            return $this->redirect('/members/' . $id)->withError('لا يمكن تعديل بيانات العضو بعد تفعيل العضوية — تواصل مع المشرف العام');
        }

        $salesReps = $db->select("SELECT id, code, name_ar FROM sales_representatives WHERE is_active = 1 ORDER BY name_ar", []);

        return $this->view('Members.Views.edit', [
            'member'           => $member,
            'branches'         => $db->select("SELECT id, name_ar FROM branches WHERE is_active = 1"),
            '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"),
            'countries'        => $db->select("SELECT nationality_ar FROM countries WHERE is_active = 1 ORDER BY name_ar"),
            'isSuperAdmin'     => self::isSuperAdmin(),
            'specialDiscounts' => SpecialDiscount::allActive(),
            'salesReps'        => $salesReps,
        ]);
    }

    public function update(Request $request, string $id): Response
    {
        $db = App::getInstance()->db();
        $member = Member::find((int) $id);
        if (!$member) return $this->redirect('/members')->withError('العضو غير موجود');

        $isSuperAdmin = self::isSuperAdmin();

        if (!empty($member->membership_number) && !$isSuperAdmin) {
            return $this->redirect('/members/' . $id)->withError('لا يمكن تعديل بيانات العضو بعد تفعيل العضوية — تواصل مع المشرف العام');
        }

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

        $allowed = ['full_name_en','phone_home','phone_mobile','phone_international','email','emergency_name','emergency_phone','residence_type','residence_address','landmark','floor','apartment','area','governorate','correspondence_address','employment_type','occupation','job_title','employment_date','business_address','office_phone','office_fax','business_activity','referral_source','referred_by_representative_id','religion','marital_status'];

        $update = [];
        foreach ($allowed as $f) {
            if (array_key_exists($f, $data)) {
                $v = trim((string) ($data[$f] ?? ''));
                $update[$f] = $v === '' ? null : $v;
            }
        }

        if (isset($update['phone_mobile']) && $update['phone_mobile'] !== null) {
            if (!preg_match('/^01[0125]\d{8}$/', $update['phone_mobile'])) {
                return $this->redirect('/members/' . $id . '/edit')->withError('رقم المحمول غير صالح — يجب أن يكون 11 رقم ويبدأ بـ 01');
            }
        }

        if (isset($update['referred_by_representative_id'])) {
            $update['referred_by_representative_id'] = $update['referred_by_representative_id'] !== null && $update['referred_by_representative_id'] !== '' ? (int) $update['referred_by_representative_id'] : null;
        }

        // ── SuperAdmin-only fields: full_name_ar, national_id ──
        if ($isSuperAdmin) {
            $errors = [];

            // Name change
            if (array_key_exists('full_name_ar', $data)) {
                $newName = trim((string) $data['full_name_ar']);
                if ($newName !== '' && mb_strlen($newName) >= 5) {
                    $update['full_name_ar'] = $newName;
                } elseif ($newName !== '') {
                    $errors[] = 'الاسم بالعربي يجب أن يكون 5 أحرف على الأقل';
                }
            }

            // National ID change
            if (array_key_exists('national_id', $data)) {
                $newNid = trim((string) $data['national_id']);

                if ($newNid === '') {
                    // Allow clearing NID (e.g., for passport-based members)
                    $update['national_id'] = null;
                } elseif ($newNid !== ($member->national_id ?? '')) {
                    // Validate new NID
                    $parsed = NationalIdParser::parse($newNid);
                    if (!$parsed['is_valid']) {
                        $errors = array_merge($errors, $parsed['errors']);
                    } else {
                        // Check duplicate
                        $dup = $db->selectOne(
                            "SELECT id, full_name_ar FROM members WHERE national_id = ? AND is_archived = 0 AND id != ?",
                            [$newNid, (int) $id]
                        );
                        if ($dup) {
                            $errors[] = 'الرقم القومي مسجل بالفعل للعضو: ' . $dup['full_name_ar'];
                        } else {
                            $update['national_id'] = $newNid;
                            $update['date_of_birth'] = $parsed['dob'];
                            $update['age_years'] = $parsed['age_years'];
                            $update['age_months'] = $parsed['age_months'];
                            $update['gender'] = $parsed['gender'];
                            $update['governorate_code'] = $parsed['governorate_code'];
                        }
                    }
                }
            }

            if (!empty($errors)) {
                $session = App::getInstance()->session();
                $session->flash('_alerts', array_map(fn($e) => ['type' => 'error', 'message' => $e], $errors));
                return $this->redirect('/members/' . $id . '/edit');
            }
        }

        // ── Special Discount handling ──
        if (array_key_exists('special_discount_id', $data)) {
            $discountId = trim((string) ($data['special_discount_id'] ?? ''));
            if ($discountId === '' || $discountId === '0') {
                $update['special_discount_id'] = null;
                $update['discount_amount'] = null;
            } else {
                $discountRow = $db->selectOne("SELECT * FROM special_discounts WHERE id = ? AND is_active = 1", [(int) $discountId]);
                if ($discountRow) {
                    $update['special_discount_id'] = (int) $discountId;
                    $membershipValue = $member->membership_value ?? '0.00';
                    $update['discount_amount'] = bcdiv(bcmul($membershipValue, $discountRow['discount_percentage'], 4), '100', 2);

                    // Handle document upload if required
                    if ($discountRow['requires_document'] && !empty($_FILES['discount_document']['tmp_name'])) {
                        $file = $_FILES['discount_document'];
                        $allowedTypes = ['application/pdf', 'image/jpeg', 'image/png'];
                        $finfo = new \finfo(FILEINFO_MIME_TYPE);
                        $mimeType = $finfo->file($file['tmp_name']);
                        if (in_array($mimeType, $allowedTypes) && $file['size'] <= 10485760) {
                            $ext = pathinfo($file['name'], PATHINFO_EXTENSION);
                            $storedName = 'discount_' . (int) $id . '_' . date('Ymd_His') . '_' . bin2hex(random_bytes(4)) . '.' . $ext;
                            $uploadDir = App::getInstance()->basePath() . '/storage/uploads/discounts/';
                            if (!is_dir($uploadDir)) mkdir($uploadDir, 0755, true);
                            if (move_uploaded_file($file['tmp_name'], $uploadDir . $storedName)) {
                                $update['special_discount_document'] = 'storage/uploads/discounts/' . $storedName;
                            }
                        }
                    }
                }
            }
        }

        if (!empty($_FILES['photo']) && PhotoUploadService::isUploaded($_FILES['photo'])) {
            $photoValidation = PhotoUploadService::validate($_FILES['photo']);
            if ($photoValidation['valid']) {
                $photoResult = PhotoUploadService::upload($_FILES['photo'], 'members', (int) $id);
                if ($photoResult) {
                    $update['photo_path'] = $photoResult['path'];
                }
            }
        }

        if (!empty($update)) $member->update($update);
        return $this->redirect('/members/' . $id)->withSuccess('تم تحديث البيانات');
    }

    public function uploadPhoto(Request $request, string $id): Response
    {
        $member = Member::find((int) $id);
        if (!$member || $member->is_archived) {
            return $this->redirect('/members')->withError('العضو غير موجود');
        }

        $photoFile = $_FILES['photo'] ?? [];
        if (!PhotoUploadService::isUploaded($photoFile)) {
            return $this->redirect('/members/' . $id)->withError('لم يتم رفع صورة');
        }

        $validation = PhotoUploadService::validate($photoFile);
        if (!$validation['valid']) {
            $session = App::getInstance()->session();
            $session->flash('_alerts', array_map(fn($e) => ['type' => 'error', 'message' => $e], $validation['errors']));
            return $this->redirect('/members/' . $id);
        }

        $result = PhotoUploadService::upload($photoFile, 'members', (int) $id);
        if ($result) {
            $member->update(['photo_path' => $result['path']]);
            return $this->redirect('/members/' . $id)->withSuccess('تم رفع الصورة بنجاح');
        }

        return $this->redirect('/members/' . $id)->withError('حدث خطأ أثناء حفظ الصورة');
    }

    public function deletePhoto(Request $request, string $id): Response
    {
        $member = Member::find((int) $id);
        if (!$member || $member->is_archived) {
            return $this->redirect('/members')->withError('العضو غير موجود');
        }

        if (empty($member->photo_path)) {
            return $this->redirect('/members/' . $id)->withError('لا توجد صورة لحذفها');
        }

        $fullPath = App::getInstance()->basePath() . '/public/' . $member->photo_path;
        if (is_file($fullPath)) {
            @unlink($fullPath);
        }

        $member->update(['photo_path' => null]);
        return $this->redirect('/members/' . $id)->withSuccess('تم حذف الصورة');
    }

    public function changeStatus(Request $request, string $id): Response
    {
        $member = Member::find((int) $id);
        if (!$member) return $this->redirect('/members')->withError('العضو غير موجود');
        $newStatus = trim((string) $request->post('status', ''));
        if (!in_array($newStatus, array_keys(Member::getStatusOptions()))) return $this->redirect('/members/' . $id)->withError('حالة غير صالحة');
        $old = $member->status; $member->update(['status' => $newStatus]);
        try {
            WorkflowEngine::transitionByEntity('members', (int) $id, $newStatus);
        } catch (\Throwable $e) {
            Logger::warning("Workflow transition failed for member status change", ['member_id' => (int) $id, 'transition' => $newStatus, 'error' => $e->getMessage()]);
        }
        EventBus::dispatch('member.status_changed', ['member_id' => (int) $id, 'old' => $old, 'new' => $newStatus]);
        return $this->redirect('/members/' . $id)->withSuccess('تم تغيير الحالة');
    }

    public function search(Request $request): Response
    {
        $q = trim((string) $request->get('q', ''));
        return $this->view('Members.Views.search', ['query' => $q, 'results' => ($q !== '' && mb_strlen($q) >= 2) ? MemberSearchService::search($q, 50) : []]);
    }

    public function changelog(Request $request, string $id): Response
    {
        $db = App::getInstance()->db();
        $member = Member::find((int) $id);
        if (!$member) {
            return $this->redirect('/members')->withError('العضو غير موجود');
        }

        $spouseIds = array_column(
            $db->select("SELECT id FROM spouses WHERE member_id = ? ORDER BY id", [(int) $id]),
            'id'
        );
        $childIds = array_column(
            $db->select("SELECT id FROM children WHERE member_id = ? ORDER BY id", [(int) $id]),
            'id'
        );
        $tempIds = array_column(
            $db->select("SELECT id FROM temporary_members WHERE member_id = ? ORDER BY id", [(int) $id]),
            'id'
        );

        $memberLogs = $db->select(
            "SELECT * FROM audit_trail WHERE entity_type = 'members' AND entity_id = ? ORDER BY created_at DESC LIMIT 200",
            [(int) $id]
        );

        $spouseLogs = [];
        if (!empty($spouseIds)) {
            $placeholders = implode(',', array_fill(0, count($spouseIds), '?'));
            $spouseLogs = $db->select(
                "SELECT * FROM audit_trail WHERE entity_type = 'spouses' AND entity_id IN ({$placeholders}) ORDER BY created_at DESC LIMIT 200",
                $spouseIds
            );
        }

        $childLogs = [];
        if (!empty($childIds)) {
            $placeholders = implode(',', array_fill(0, count($childIds), '?'));
            $childLogs = $db->select(
                "SELECT * FROM audit_trail WHERE entity_type = 'children' AND entity_id IN ({$placeholders}) ORDER BY created_at DESC LIMIT 200",
                $childIds
            );
        }

        $tempLogs = [];
        if (!empty($tempIds)) {
            $placeholders = implode(',', array_fill(0, count($tempIds), '?'));
            $tempLogs = $db->select(
                "SELECT * FROM audit_trail WHERE entity_type = 'temporary_members' AND entity_id IN ({$placeholders}) ORDER BY created_at DESC LIMIT 200",
                $tempIds
            );
        }

        return $this->view('Members.Views.changelog', [
            'member'     => $member,
            'memberLogs' => $memberLogs,
            'spouseLogs' => $spouseLogs,
            'childLogs'  => $childLogs,
            'tempLogs'   => $tempLogs,
        ]);
    }

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

        $member = $db->selectOne("SELECT * FROM members WHERE id = ?", [(int) $id]);
        if (!$member) return $this->redirect('/members')->withError('العضو غير موجود');

        $memberId = (int) $id;

        // Death cases (as source or target)
        $deathCases = $db->select(
            "SELECT dc.*, m.full_name_ar as member_name, m.membership_number as member_number,
                    tm.full_name_ar as target_member_name
             FROM death_cases dc
             JOIN members m ON m.id = dc.member_id
             LEFT JOIN members tm ON tm.id = dc.transferred_to_member_id
             WHERE dc.member_id = ? OR dc.transferred_to_member_id = ?
             ORDER BY dc.created_at DESC",
            [$memberId, $memberId]
        );

        // Waiver requests (as source or target)
        $waiverRequests = $db->select(
            "SELECT wr.*, sm.full_name_ar as source_member_name, sm.membership_number as source_number,
                    tm.full_name_ar as target_member_name, tm.membership_number as target_number,
                    e.full_name_ar as approved_by_name
             FROM waiver_requests wr
             JOIN members sm ON sm.id = wr.source_member_id
             LEFT JOIN members tm ON tm.id = wr.target_member_id
             LEFT JOIN employees e ON e.id = wr.approved_by
             WHERE wr.source_member_id = ? OR wr.target_member_id = ?
             ORDER BY wr.created_at DESC",
            [$memberId, $memberId]
        );

        // Transfer requests (as source or target)
        $transferRequests = $db->select(
            "SELECT tr.*, sm.full_name_ar as source_member_name, sm.membership_number as source_number,
                    tm.full_name_ar as target_member_name, tm.membership_number as target_number,
                    e.full_name_ar as approved_by_name
             FROM transfer_requests tr
             JOIN members sm ON sm.id = tr.source_member_id
             LEFT JOIN members tm ON tm.id = tr.target_member_id
             LEFT JOIN employees e ON e.id = tr.approved_by
             WHERE tr.source_member_id = ? OR tr.target_member_id = ?
             ORDER BY tr.created_at DESC",
            [$memberId, $memberId]
        );

        // Death audit logs (detailed steps)
        $deathAuditLogs = \App\Modules\Death\Services\DeathAuditService::getForMember($memberId);

        // Payments related to all procedures
        $procedurePayments = $db->select(
            "SELECT p.*
             FROM payments p
             WHERE p.member_id = ? AND p.is_voided = 0
               AND p.payment_type IN ('death_fee','waiver_fee','transfer_fee','separation_fee')
             ORDER BY p.payment_date DESC",
            [$memberId]
        );

        // Archive snapshots for this member
        $snapshots = $db->select(
            "SELECT id, snapshot_reason, membership_number, snapshot_taken_at
             FROM archive_snapshots WHERE entity_type = 'members' AND entity_id = ?
             ORDER BY snapshot_taken_at DESC",
            [$memberId]
        );

        // Build unified timeline from ALL procedure data
        $timeline = [];

        // Death audit logs (already detailed)
        foreach ($deathAuditLogs as $log) {
            $timeline[] = [
                'type' => 'death',
                'icon' => '&#x1f54a;',
                'description' => $log['action_description_ar'],
                'date' => $log['performed_at'],
                'performer' => $log['performer_name'] ?? null,
                'entity' => !empty($log['affected_entity_type']) ? $log['affected_entity_type'] . ' #' . $log['affected_entity_id'] : null,
            ];
        }

        // Waiver timeline (reconstruct from request data)
        foreach ($waiverRequests as $wr) {
            $isSource = ((int) $wr['source_member_id'] === $memberId);
            $roleLabel = $isSource ? '(المتنازل)' : '(المتنازل إليه)';
            $timeline[] = [
                'type' => 'waiver', 'icon' => '&#x1f4dd;',
                'description' => 'تقديم طلب تنازل #' . $wr['id'] . ' — ' . $roleLabel . ' — رقم العضوية: ' . $wr['membership_number'],
                'date' => $wr['created_at'], 'performer' => null, 'entity' => 'waiver_requests #' . $wr['id'],
            ];
            if (!empty($wr['approved_at'])) {
                $timeline[] = [
                    'type' => 'waiver', 'icon' => '&#x2705;',
                    'description' => 'اعتماد طلب التنازل #' . $wr['id'] . ' من مجلس الإدارة',
                    'date' => $wr['approved_at'], 'performer' => $wr['approved_by_name'] ?? null, 'entity' => null,
                ];
            }
            // Fee payment
            $waiverPayment = $db->selectOne(
                "SELECT payment_date, amount FROM payments WHERE payment_type = 'waiver_fee' AND related_entity_type = 'waiver_requests' AND related_entity_id = ? AND is_voided = 0 LIMIT 1",
                [(int) $wr['id']]
            );
            if ($waiverPayment) {
                $timeline[] = [
                    'type' => 'waiver', 'icon' => '&#x1f4b0;',
                    'description' => 'سداد رسوم التنازل #' . $wr['id'] . ' — ' . money($waiverPayment['amount']),
                    'date' => $waiverPayment['payment_date'], 'performer' => null, 'entity' => null,
                ];
            }
            if ($wr['status'] === 'completed') {
                $timeline[] = [
                    'type' => 'waiver', 'icon' => '&#x1f4e6;',
                    'description' => 'أخذ لقطة أرشيف للعضوية الأصلية (العضو المتنازل: ' . ($wr['source_member_name'] ?? '') . ')',
                    'date' => $wr['updated_at'], 'performer' => null, 'entity' => 'archive_snapshots',
                ];
                $timeline[] = [
                    'type' => 'waiver', 'icon' => '&#x1f46a;',
                    'description' => 'نقل التابعين (الزوجات، الأبناء، الأعضاء المؤقتين) إلى العضوية الجديدة',
                    'date' => $wr['updated_at'], 'performer' => null, 'entity' => 'members #' . ($wr['target_member_id'] ?? ''),
                ];
                $timeline[] = [
                    'type' => 'waiver', 'icon' => '&#x1f4b3;',
                    'description' => 'نقل البيانات المالية (المدفوعات، الاشتراكات، الأقساط، الغرامات) إلى العضوية الجديدة',
                    'date' => $wr['updated_at'], 'performer' => null, 'entity' => null,
                ];
                $timeline[] = [
                    'type' => 'waiver', 'icon' => '&#x1f4c4;',
                    'description' => 'نقل المستندات والمرفقات إلى العضوية الجديدة',
                    'date' => $wr['updated_at'], 'performer' => null, 'entity' => null,
                ];
                $timeline[] = [
                    'type' => 'waiver', 'icon' => '&#x2728;',
                    'description' => 'تفعيل العضوية الجديدة باسم: ' . ($wr['target_member_name'] ?? '—') . ' — رقم العضوية: ' . $wr['membership_number'],
                    'date' => $wr['updated_at'], 'performer' => null, 'entity' => 'members #' . ($wr['target_member_id'] ?? ''),
                ];
                $timeline[] = [
                    'type' => 'waiver', 'icon' => '&#x1f512;',
                    'description' => 'أرشفة العضوية الأصلية (المتنازل: ' . ($wr['source_member_name'] ?? '') . ') — الحالة: متنازل',
                    'date' => $wr['updated_at'], 'performer' => null, 'entity' => 'members #' . $wr['source_member_id'],
                ];
                $timeline[] = [
                    'type' => 'waiver', 'icon' => '&#x2705;',
                    'description' => 'إتمام عملية التنازل #' . $wr['id'] . ' بنجاح',
                    'date' => $wr['updated_at'], 'performer' => null, 'entity' => null,
                ];
            }
        }

        // Transfer timeline (reconstruct from request data)
        foreach ($transferRequests as $tr) {
            $isSource = ((int) $tr['source_member_id'] === $memberId);
            $trTypeLabel = match($tr['transfer_type'] ?? '') {
                'full_transfer' => 'نقل كامل', 'child_separation' => 'فصل أبناء',
                'child_mandatory_25' => 'فصل إلزامي', default => $tr['transfer_type'] ?? '',
            };
            $roleLabel = $isSource ? '(العضوية الأصلية)' : '(العضوية الجديدة)';
            $timeline[] = [
                'type' => 'transfer', 'icon' => '&#x1f504;',
                'description' => 'تقديم طلب ' . $trTypeLabel . ' #' . $tr['id'] . ' — ' . $roleLabel,
                'date' => $tr['created_at'], 'performer' => null, 'entity' => 'transfer_requests #' . $tr['id'],
            ];
            if (!empty($tr['approved_at'])) {
                $timeline[] = [
                    'type' => 'transfer', 'icon' => '&#x2705;',
                    'description' => 'اعتماد طلب ' . $trTypeLabel . ' #' . $tr['id'],
                    'date' => $tr['approved_at'], 'performer' => $tr['approved_by_name'] ?? null, 'entity' => null,
                ];
            }
            $trPayment = $db->selectOne(
                "SELECT payment_date, amount FROM payments WHERE payment_type IN ('transfer_fee','separation_fee') AND related_entity_type = 'transfer_requests' AND related_entity_id = ? AND is_voided = 0 LIMIT 1",
                [(int) $tr['id']]
            );
            if ($trPayment) {
                $timeline[] = [
                    'type' => 'transfer', 'icon' => '&#x1f4b0;',
                    'description' => 'سداد رسوم ' . $trTypeLabel . ' #' . $tr['id'] . ' — ' . money($trPayment['amount']),
                    'date' => $trPayment['payment_date'], 'performer' => null, 'entity' => null,
                ];
            }
            if ($tr['status'] === 'completed' && !empty($tr['completed_at'])) {
                $timeline[] = [
                    'type' => 'transfer', 'icon' => '&#x2705;',
                    'description' => 'إتمام عملية ' . $trTypeLabel . ' #' . $tr['id'] . ' بنجاح',
                    'date' => $tr['completed_at'], 'performer' => null, 'entity' => null,
                ];
            }
        }

        // Sort timeline by date
        usort($timeline, fn($a, $b) => strcmp($a['date'] ?? '', $b['date'] ?? ''));

        return $this->view('Members.Views.insurance-record', [
            'member'            => (object) $member,
            'deathCases'        => $deathCases,
            'waiverRequests'    => $waiverRequests,
            'transferRequests'  => $transferRequests,
            'timeline'          => $timeline,
            'procedurePayments' => $procedurePayments,
            'snapshots'         => $snapshots,
        ]);
    }
}