<?php
class MemberApiController extends Controller
{
    public function datatable(Request $request): void
    {
        $db = Database::getInstance();

        $draw    = (int) $request->get('draw', 1);
        $start   = (int) $request->get('start', 0);
        $length  = (int) $request->get('length', 25);
        $search  = $request->get('search')['value'] ?? '';
        $orderCol = (int) ($request->get('order')[0]['column'] ?? 0);
        $orderDir = strtolower($request->get('order')[0]['dir'] ?? 'desc') === 'asc' ? 'ASC' : 'DESC';

        // Filters
        $filterStatus = $request->get('filter_status', '');
        $filterType   = $request->get('filter_type', '');
        $filterGender = $request->get('filter_gender', '');
        $filterFrom   = $request->get('filter_date_from', '');
        $filterTo     = $request->get('filter_date_to', '');

        $columns = ['m.id', 'm.membership_number', 'm.full_name_en', 'm.national_id', 'm.primary_phone', 'mt.name_en', 'm.status', 'm.join_date'];
        $orderColumn = $columns[$orderCol] ?? 'm.id';

        $baseWhere = "m.is_deleted = 0";
        $params = [];

        if ($filterStatus !== '') {
            $baseWhere .= " AND m.status = ?";
            $params[] = $filterStatus;
        }
        if ($filterType !== '') {
            $baseWhere .= " AND m.membership_type_id = ?";
            $params[] = (int) $filterType;
        }
        if ($filterGender !== '') {
            $baseWhere .= " AND m.gender = ?";
            $params[] = $filterGender;
        }
        if ($filterFrom !== '') {
            $baseWhere .= " AND m.join_date >= ?";
            $params[] = $filterFrom;
        }
        if ($filterTo !== '') {
            $baseWhere .= " AND m.join_date <= ?";
            $params[] = $filterTo;
        }

        $searchWhere = '';
        $searchParams = [];
        if ($search !== '') {
            $searchWhere = " AND (m.membership_number LIKE ? OR m.full_name_en LIKE ? OR m.full_name_ar LIKE ? OR m.national_id LIKE ? OR m.primary_phone LIKE ?)";
            $s = "%{$search}%";
            $searchParams = [$s, $s, $s, $s, $s];
        }

        $totalCount = (int) $db->fetchColumn("SELECT COUNT(*) FROM members m WHERE {$baseWhere}", $params);

        $filteredCount = (int) $db->fetchColumn(
            "SELECT COUNT(*) FROM members m LEFT JOIN membership_types mt ON m.membership_type_id = mt.id WHERE {$baseWhere}{$searchWhere}",
            array_merge($params, $searchParams)
        );

        $rows = $db->fetchAll(
            "SELECT m.id, m.membership_number, m.full_name_en, m.full_name_ar, m.national_id, m.primary_phone,
                    m.status, m.join_date, m.photo_path, m.vip, m.gender,
                    mt.name_en AS type_name_en, mt.name_ar AS type_name_ar
             FROM members m
             LEFT JOIN membership_types mt ON m.membership_type_id = mt.id
             WHERE {$baseWhere}{$searchWhere}
             ORDER BY {$orderColumn} {$orderDir}
             LIMIT " . (int)$length . " OFFSET " . (int)$start,
            array_merge($params, $searchParams)
        );

        $data = [];
        foreach ($rows as $row) {
            $actions = '<div class="btn-group btn-group-sm">';
            $actions .= '<a href="/members/' . $row['id'] . '" class="btn btn-outline-primary" title="View"><i class="fas fa-eye"></i></a>';
            if (Gate::can('members.edit')) {
                $actions .= '<a href="/members/' . $row['id'] . '/edit" class="btn btn-outline-warning" title="Edit"><i class="fas fa-edit"></i></a>';
            }
            if (Gate::can('members.delete')) {
                $actions .= '<button class="btn btn-outline-danger btn-delete-member" data-id="' . $row['id'] . '" title="Delete"><i class="fas fa-trash"></i></button>';
            }
            $actions .= '</div>';

            $photo = $row['photo_path']
                ? '<img src="/uploads/' . e($row['photo_path']) . '" class="table-avatar" alt="">'
                : '<span class="table-avatar d-inline-flex align-items-center justify-content-center bg-secondary text-white rounded-circle" style="width:36px;height:36px;font-size:14px"><i class="fas fa-user"></i></span>';

            $nameHtml = '<div>' . e($row['full_name_en']);
            if ($row['vip']) {
                $nameHtml .= ' <i class="fas fa-star text-warning" title="VIP"></i>';
            }
            $nameHtml .= '</div>';
            if ($row['full_name_ar']) {
                $nameHtml .= '<small class="text-muted">' . e($row['full_name_ar']) . '</small>';
            }

            $data[] = [
                'id'                => $row['id'],
                'photo'             => $photo,
                'membership_number' => '<a href="/members/' . $row['id'] . '">' . e($row['membership_number']) . '</a>',
                'name'              => $nameHtml,
                'national_id'       => e($row['national_id'] ?? '—'),
                'phone'             => e($row['primary_phone'] ?? '—'),
                'type'              => e(current_lang() === 'ar' ? $row['type_name_ar'] : $row['type_name_en']),
                'status'            => status_badge($row['status']),
                'join_date'         => format_date($row['join_date']),
                'actions'           => $actions,
            ];
        }

        $this->json([
            'draw'            => $draw,
            'recordsTotal'    => $totalCount,
            'recordsFiltered' => $filteredCount,
            'data'            => $data,
        ]);
    }

    public function search(Request $request): void
    {
        $q    = $request->get('q', '');
        $page = (int) $request->get('page', 1);
        $limit = 20;
        $offset = ($page - 1) * $limit;

        if (mb_strlen($q) < 1) {
            $this->json(['results' => [], 'pagination' => ['more' => false]]);
            return;
        }

        $db = Database::getInstance();
        $s = "%{$q}%";
        $rows = $db->fetchAll(
            "SELECT m.id, m.membership_number, m.full_name_en, m.full_name_ar, m.national_id, m.primary_phone, m.status, m.photo_path
             FROM members m
             WHERE m.is_deleted = 0 AND m.status IN ('active','frozen','suspended','pending_approval')
               AND (m.membership_number LIKE ? OR m.full_name_en LIKE ? OR m.full_name_ar LIKE ? OR m.national_id LIKE ? OR m.primary_phone LIKE ?)
             ORDER BY m.full_name_en
             LIMIT " . ($limit + 1) . " OFFSET " . (int)$offset,
            [$s, $s, $s, $s, $s]
        );

        $more = count($rows) > $limit;
        if ($more) array_pop($rows);

        $results = [];
        foreach ($rows as $row) {
            $results[] = [
                'id'                => $row['id'],
                'text'              => $row['membership_number'] . ' — ' . $row['full_name_en'],
                'membership_number' => $row['membership_number'],
                'name_en'           => $row['full_name_en'],
                'name_ar'           => $row['full_name_ar'],
                'national_id'       => $row['national_id'],
                'phone'             => $row['primary_phone'],
                'status'            => $row['status'],
            ];
        }

        $this->json(['results' => $results, 'pagination' => ['more' => $more]]);
    }

    public function stats(Request $request): void
    {
        $db = Database::getInstance();
        $active    = (int) $db->fetchColumn("SELECT COUNT(*) FROM members WHERE status = 'active' AND is_deleted = 0");
        $frozen    = (int) $db->fetchColumn("SELECT COUNT(*) FROM members WHERE status = 'frozen' AND is_deleted = 0");
        $suspended = (int) $db->fetchColumn("SELECT COUNT(*) FROM members WHERE status = 'suspended' AND is_deleted = 0");
        $newMonth  = (int) $db->fetchColumn("SELECT COUNT(*) FROM members WHERE status = 'active' AND is_deleted = 0 AND MONTH(activation_date) = MONTH(CURDATE()) AND YEAR(activation_date) = YEAR(CURDATE())");

        $this->json([
            'success'   => true,
            'active'    => $active,
            'frozen'    => $frozen,
            'suspended' => $suspended,
            'new_month' => $newMonth,
        ]);
    }

    public function store(Request $request): void
    {
        if (!CSRF::validate($request->csrfToken())) {
            $this->json(['success' => false, 'message' => 'Invalid CSRF token'], 403);
            return;
        }

        $validator = new Validator($request->all(), [
            'membership_type_id' => 'required|integer|exists:membership_types,id',
            'title'              => 'nullable|in:mr,mrs,ms,dr,eng,prof,sheikh,other',
            'first_name_en'      => 'required|string|max:100',
            'second_name_en'     => 'nullable|string|max:100',
            'third_name_en'      => 'nullable|string|max:100',
            'last_name_en'       => 'required|string|max:100',
            'first_name_ar'      => 'nullable|string|max:100',
            'second_name_ar'     => 'nullable|string|max:100',
            'third_name_ar'      => 'nullable|string|max:100',
            'last_name_ar'       => 'nullable|string|max:100',
            'national_id'        => 'nullable|string|max:20',
            'passport_number'    => 'nullable|string|max:30',
            'nationality_id'     => 'nullable|integer',
            'date_of_birth'      => 'nullable|date',
            'gender'             => 'required|in:male,female',
            'marital_status'     => 'nullable|in:single,married,divorced,widowed',
            'primary_phone'      => 'nullable|string|max:20',
            'secondary_phone'    => 'nullable|string|max:20',
            'primary_email'      => 'nullable|email|max:191',
            'address_line1'      => 'nullable|string|max:500',
            'city_id'            => 'nullable|integer',
            'country_id'         => 'nullable|integer',
            'occupation'         => 'nullable|string|max:200',
            'employer'           => 'nullable|string|max:300',
            'job_title'          => 'nullable|string|max:200',
            'emergency_contact_name'  => 'nullable|string|max:200',
            'emergency_contact_phone' => 'nullable|string|max:20',
            'emergency_contact_relation' => 'nullable|string|max:50',
            'vip'                => 'nullable|boolean',
            'internal_notes'     => 'nullable|string',
        ]);

        if ($validator->fails()) {
            $this->json(['success' => false, 'errors' => $validator->errors()], 422);
            return;
        }

        $data = $validator->validated();
        $db = Database::getInstance();

        // Check duplicate national ID
        if (!empty($data['national_id'])) {
            $existing = $db->fetchOne("SELECT id, membership_number, status FROM members WHERE national_id = ? AND is_deleted = 0", [$data['national_id']]);
            if ($existing) {
                $this->json(['success' => false, 'errors' => ['national_id' => ['National ID already exists for member ' . $existing['membership_number']]]], 422);
                return;
            }
        }

        // Generate membership number
        $membershipNumber = SequenceGenerator::next('member_number');
        if (!$membershipNumber) {
            $this->json(['success' => false, 'message' => 'Failed to generate membership number'], 500);
            return;
        }

        // Handle photo upload
        $photoPath = null;
        if ($request->file('photo') && $request->file('photo')['error'] === UPLOAD_ERR_OK) {
            $upload = new FileUpload($request->file('photo'));
            $upload->setAllowedTypes(['jpg', 'jpeg', 'png', 'webp'])
                   ->setMaxSize(5 * 1024 * 1024)
                   ->setDestination('uploads/members/photos');
            if ($upload->validate()) {
                $photoPath = $upload->save();
            }
        }

        $db->beginTransaction();
        try {
            $fullNameEn = trim(implode(' ', array_filter([
                $data['first_name_en'] ?? '', $data['second_name_en'] ?? '',
                $data['third_name_en'] ?? '', $data['last_name_en'] ?? ''
            ])));
            $fullNameAr = trim(implode(' ', array_filter([
                $data['first_name_ar'] ?? '', $data['second_name_ar'] ?? '',
                $data['third_name_ar'] ?? '', $data['last_name_ar'] ?? ''
            ])));

            $insertData = [
                'membership_number'   => $membershipNumber,
                'membership_type_id'  => $data['membership_type_id'],
                'title'               => $data['title'] ?? null,
                'first_name_en'       => $data['first_name_en'],
                'second_name_en'      => $data['second_name_en'] ?? null,
                'third_name_en'       => $data['third_name_en'] ?? null,
                'last_name_en'        => $data['last_name_en'],
                'full_name_en'        => $fullNameEn,
                'first_name_ar'       => $data['first_name_ar'] ?? null,
                'second_name_ar'      => $data['second_name_ar'] ?? null,
                'third_name_ar'       => $data['third_name_ar'] ?? null,
                'last_name_ar'        => $data['last_name_ar'] ?? null,
                'full_name_ar'        => $fullNameAr ?: null,
                'national_id'         => $data['national_id'] ?? null,
                'passport_number'     => $data['passport_number'] ?? null,
                'nationality_id'      => $data['nationality_id'] ?? null,
                'date_of_birth'       => $data['date_of_birth'] ?? null,
                'gender'              => $data['gender'],
                'marital_status'      => $data['marital_status'] ?? null,
                'primary_phone'       => $data['primary_phone'] ?? null,
                'secondary_phone'     => $data['secondary_phone'] ?? null,
                'primary_email'       => $data['primary_email'] ?? null,
                'address_line1'       => $data['address_line1'] ?? null,
                'city_id'             => $data['city_id'] ?? null,
                'country_id'          => $data['country_id'] ?? null,
                'occupation'          => $data['occupation'] ?? null,
                'employer'            => $data['employer'] ?? null,
                'job_title'           => $data['job_title'] ?? null,
                'emergency_contact_name'     => $data['emergency_contact_name'] ?? null,
                'emergency_contact_phone'    => $data['emergency_contact_phone'] ?? null,
                'emergency_contact_relation' => $data['emergency_contact_relation'] ?? null,
                'photo_path'          => $photoPath,
                'vip'                 => !empty($data['vip']) ? 1 : 0,
                'internal_notes'      => $data['internal_notes'] ?? null,
                'status'              => 'active',
                'join_date'           => date('Y-m-d'),
                'activation_date'     => date('Y-m-d'),
                'created_by'          => Auth::id(),
                'communication_preference' => 'all',
                'language_preference' => 'ar',
            ];

            $memberId = $db->insert('members', $insertData);

            // Create member_balances record
            $db->insert('member_balances', ['member_id' => $memberId]);

            // Create family record if family membership
            $memType = $db->fetchOne("SELECT is_family_membership FROM membership_types WHERE id = ?", [$data['membership_type_id']]);
            if ($memType && $memType['is_family_membership']) {
                $familyCode = 'FAM-' . str_pad($memberId, 6, '0', STR_PAD_LEFT);
                $familyId = $db->insert('member_families', [
                    'family_code'       => $familyCode,
                    'primary_member_id' => $memberId,
                    'family_name_en'    => $fullNameEn,
                    'family_name_ar'    => $fullNameAr ?: null,
                    'created_by'        => Auth::id(),
                ]);
                $db->update('members', ['family_id' => $familyId, 'is_primary_member' => 1], 'id = ?', [$memberId]);
            }

            AuditLog::log('create', 'members', 'members', $memberId, "Created member {$membershipNumber}", null, $insertData);

            $db->commit();

            $this->json([
                'success'  => true,
                'message'  => 'Member created successfully: ' . $membershipNumber,
                'redirect' => '/members/' . $memberId,
            ]);
        } catch (\Exception $e) {
            $db->rollBack();
            Logger::error('Member creation failed: ' . $e->getMessage());
            $this->json(['success' => false, 'message' => 'Failed to create member: ' . $e->getMessage()], 500);
        }
    }

    public function update(Request $request): void
    {
        if (!CSRF::validate($request->csrfToken())) {
            $this->json(['success' => false, 'message' => 'Invalid CSRF token'], 403);
            return;
        }

        $id = (int) $request->routeParam('id');
        $db = Database::getInstance();
        $old = $db->fetchOne("SELECT * FROM members WHERE id = ? AND is_deleted = 0", [$id]);
        if (!$old) {
            $this->json(['success' => false, 'message' => 'Member not found'], 404);
            return;
        }

        $validator = new Validator($request->all(), [
            'membership_type_id' => 'required|integer|exists:membership_types,id',
            'title'              => 'nullable|in:mr,mrs,ms,dr,eng,prof,sheikh,other',
            'first_name_en'      => 'required|string|max:100',
            'second_name_en'     => 'nullable|string|max:100',
            'third_name_en'      => 'nullable|string|max:100',
            'last_name_en'       => 'required|string|max:100',
            'first_name_ar'      => 'nullable|string|max:100',
            'second_name_ar'     => 'nullable|string|max:100',
            'third_name_ar'      => 'nullable|string|max:100',
            'last_name_ar'       => 'nullable|string|max:100',
            'national_id'        => 'nullable|string|max:20',
            'passport_number'    => 'nullable|string|max:30',
            'nationality_id'     => 'nullable|integer',
            'date_of_birth'      => 'nullable|date',
            'gender'             => 'required|in:male,female',
            'marital_status'     => 'nullable|in:single,married,divorced,widowed',
            'primary_phone'      => 'nullable|string|max:20',
            'secondary_phone'    => 'nullable|string|max:20',
            'primary_email'      => 'nullable|email|max:191',
            'address_line1'      => 'nullable|string|max:500',
            'city_id'            => 'nullable|integer',
            'country_id'         => 'nullable|integer',
            'occupation'         => 'nullable|string|max:200',
            'employer'           => 'nullable|string|max:300',
            'job_title'          => 'nullable|string|max:200',
            'emergency_contact_name'  => 'nullable|string|max:200',
            'emergency_contact_phone' => 'nullable|string|max:20',
            'emergency_contact_relation' => 'nullable|string|max:50',
            'vip'                => 'nullable|boolean',
            'internal_notes'     => 'nullable|string',
        ]);

        if ($validator->fails()) {
            $this->json(['success' => false, 'errors' => $validator->errors()], 422);
            return;
        }

        $data = $validator->validated();

        // Duplicate national ID check (exclude self)
        if (!empty($data['national_id'])) {
            $dup = $db->fetchOne("SELECT id FROM members WHERE national_id = ? AND id != ? AND is_deleted = 0", [$data['national_id'], $id]);
            if ($dup) {
                $this->json(['success' => false, 'errors' => ['national_id' => ['National ID already used by another member']]], 422);
                return;
            }
        }

        // Handle photo
        if ($request->file('photo') && $request->file('photo')['error'] === UPLOAD_ERR_OK) {
            $upload = new FileUpload($request->file('photo'));
            $upload->setAllowedTypes(['jpg', 'jpeg', 'png', 'webp'])
                   ->setMaxSize(5 * 1024 * 1024)
                   ->setDestination('uploads/members/photos');
            if ($upload->validate()) {
                $data['photo_path'] = $upload->save();
            }
        }

        $data['vip'] = !empty($data['vip']) ? 1 : 0;
        $data['updated_by'] = Auth::id();

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

        AuditLog::log('update', 'members', 'members', $id, "Updated member {$old['membership_number']}", $old, $data);

        $this->json([
            'success'  => true,
            'message'  => 'Member updated successfully',
            'redirect' => '/members/' . $id,
        ]);
    }

    public function delete(Request $request): void
    {
        if (!CSRF::validate($request->csrfToken())) {
            $this->json(['success' => false, 'message' => 'Invalid CSRF token'], 403);
            return;
        }

        $id = (int) $request->routeParam('id');
        $db = Database::getInstance();
        $member = $db->fetchOne("SELECT * FROM members WHERE id = ? AND is_deleted = 0", [$id]);
        if (!$member) {
            $this->json(['success' => false, 'message' => 'Member not found'], 404);
            return;
        }

        $db->update('members', [
            'is_deleted' => 1,
            'deleted_at' => date('Y-m-d H:i:s'),
            'deleted_by' => Auth::id(),
        ], 'id = ?', [$id]);

        AuditLog::log('delete', 'members', 'members', $id, "Soft deleted member {$member['membership_number']}");

        $this->json(['success' => true, 'message' => 'Member deleted successfully']);
    }

    public function changeStatus(Request $request): void
    {
        if (!CSRF::validate($request->csrfToken())) {
            $this->json(['success' => false, 'message' => 'Invalid CSRF token'], 403);
            return;
        }

        $id     = (int) $request->routeParam('id');
        $status = $request->post('status', '');
        $reason = $request->post('reason', '');

        $db = Database::getInstance();

        $validStatuses = ['active', 'suspended', 'frozen', 'terminated', 'deceased'];
        if (!in_array($status, $validStatuses)) {
            $this->json(['success' => false, 'message' => 'Invalid status'], 422);
            return;
        }

        try {
            $result = '';
            if ($status === 'active') {
                $db->execute("CALL sp_activate_member(?, ?, ?, @result)", [$id, $reason, Auth::id()]);
                $r = $db->fetchOne("SELECT @result AS res");
                $result = $r['res'] ?? '';
            } elseif ($status === 'suspended') {
                $db->execute("CALL sp_suspend_member(?, ?, ?, @result)", [$id, $reason, Auth::id()]);
                $r = $db->fetchOne("SELECT @result AS res");
                $result = $r['res'] ?? '';
            } elseif ($status === 'terminated') {
                $db->execute("CALL sp_terminate_member(?, ?, ?, @result)", [$id, $reason, Auth::id()]);
                $r = $db->fetchOne("SELECT @result AS res");
                $result = $r['res'] ?? '';
            } else {
                $db->update('members', [
                    'status'            => $status,
                    'status_reason'     => $reason,
                    'status_changed_at' => date('Y-m-d H:i:s'),
                    'status_changed_by' => Auth::id(),
                    'updated_by'        => Auth::id(),
                ], 'id = ?', [$id]);
                $result = 'Status changed to ' . $status;
            }

            AuditLog::log('update', 'members', 'members', $id, "Status changed to {$status}: {$reason}");

            $this->json(['success' => true, 'message' => $result]);
        } catch (\Exception $e) {
            Logger::error('Status change failed: ' . $e->getMessage());
            $this->json(['success' => false, 'message' => 'Failed: ' . $e->getMessage()], 500);
        }
    }

    public function financial(Request $request): void
    {
        $id = (int) $request->routeParam('id');
        $db = Database::getInstance();

        $balances = $db->fetchOne("SELECT * FROM member_balances WHERE member_id = ?", [$id]);
        $ledger   = $db->fetchAll(
            "SELECT ml.*, c.currency_code FROM member_ledger ml
             LEFT JOIN sys_currencies c ON ml.currency_id = c.id
             WHERE ml.member_id = ? ORDER BY ml.transaction_date DESC, ml.id DESC LIMIT 50", [$id]
        );

        $this->json(['success' => true, 'balances' => $balances, 'ledger' => $ledger]);
    }

    public function export(Request $request): void
    {
        $db = Database::getInstance();
        $filterStatus = $request->get('status', '');
        $filterType   = $request->get('type', '');

        $where = "m.is_deleted = 0";
        $params = [];
        if ($filterStatus) { $where .= " AND m.status = ?"; $params[] = $filterStatus; }
        if ($filterType)   { $where .= " AND m.membership_type_id = ?"; $params[] = (int)$filterType; }

        $rows = $db->fetchAll(
            "SELECT m.membership_number, m.full_name_en, m.full_name_ar, m.national_id,
                    m.primary_phone, m.primary_email, m.gender, m.date_of_birth, m.status,
                    m.join_date, mt.name_en AS type_name, m.outstanding_balance
             FROM members m LEFT JOIN membership_types mt ON m.membership_type_id = mt.id
             WHERE {$where} ORDER BY m.membership_number",
            $params
        );

        $format = $request->get('format', 'csv');
        $headers = ['Membership #', 'Name (EN)', 'Name (AR)', 'National ID', 'Phone', 'Email', 'Gender', 'DOB', 'Status', 'Join Date', 'Type', 'Balance'];

        AuditLog::log('export', 'members', 'members', null, "Exported " . count($rows) . " members as {$format}");

        if ($format === 'excel') {
            Export::excel('members_' . date('Ymd') . '.xls', $headers, $rows);
        } else {
            Export::csv('members_' . date('Ymd') . '.csv', $headers, $rows);
        }
    }

    public function addNote(Request $request): void
    {
        if (!CSRF::validate($request->csrfToken())) {
            $this->json(['success' => false, 'message' => 'Invalid CSRF token'], 403);
            return;
        }

        $id = (int) $request->routeParam('id');
        $validator = new Validator($request->all(), [
            'note_type' => 'required|in:general,financial,disciplinary,medical,vip,internal',
            'subject'   => 'nullable|string|max:300',
            'note_text' => 'required|string|min:1',
        ]);

        if ($validator->fails()) {
            $this->json(['success' => false, 'errors' => $validator->errors()], 422);
            return;
        }

        $db = Database::getInstance();
        $noteId = $db->insert('member_notes', [
            'member_id'  => $id,
            'note_type'  => $request->post('note_type'),
            'subject'    => $request->post('subject'),
            'note_text'  => $request->post('note_text'),
            'is_pinned'  => $request->post('is_pinned') ? 1 : 0,
            'is_confidential' => $request->post('is_confidential') ? 1 : 0,
            'created_by' => Auth::id(),
        ]);

        $this->json(['success' => true, 'message' => 'Note added', 'id' => $noteId]);
    }

    public function deleteNote(Request $request): void
    {
        $noteId = (int) $request->routeParam('noteId');
        $db = Database::getInstance();
        $db->update('member_notes', ['is_deleted' => 1], 'id = ?', [$noteId]);
        $this->json(['success' => true, 'message' => 'Note deleted']);
    }

    public function addTag(Request $request): void
    {
        if (!CSRF::validate($request->csrfToken())) {
            $this->json(['success' => false, 'message' => 'Invalid CSRF token'], 403);
            return;
        }

        $id      = (int) $request->routeParam('id');
        $tagName = trim($request->post('tag_name', ''));
        if (!$tagName) {
            $this->json(['success' => false, 'message' => 'Tag name is required'], 422);
            return;
        }

        $db = Database::getInstance();
        $exists = $db->fetchOne("SELECT id FROM member_tags WHERE member_id = ? AND tag_name = ?", [$id, $tagName]);
        if ($exists) {
            $this->json(['success' => false, 'message' => 'Tag already exists'], 422);
            return;
        }

        $tagId = $db->insert('member_tags', [
            'member_id'  => $id,
            'tag_name'   => $tagName,
            'tag_color'  => $request->post('tag_color', '#6c757d'),
            'created_by' => Auth::id(),
        ]);

        $this->json(['success' => true, 'message' => 'Tag added', 'id' => $tagId]);
    }

    public function deleteTag(Request $request): void
    {
        $tagId = (int) $request->routeParam('tagId');
        Database::getInstance()->delete('member_tags', 'id = ?', [$tagId]);
        $this->json(['success' => true, 'message' => 'Tag removed']);
    }

    public function uploadDocument(Request $request): void
    {
        if (!CSRF::validate($request->csrfToken())) {
            $this->json(['success' => false, 'message' => 'Invalid CSRF token'], 403);
            return;
        }

        $id = (int) $request->routeParam('id');
        $file = $request->file('document');
        if (!$file || $file['error'] !== UPLOAD_ERR_OK) {
            $this->json(['success' => false, 'message' => 'No file uploaded'], 422);
            return;
        }

        $upload = new FileUpload($file);
        $upload->setAllowedTypes(['jpg', 'jpeg', 'png', 'pdf', 'doc', 'docx'])
               ->setMaxSize(10 * 1024 * 1024)
               ->setDestination('uploads/members/documents');

        if (!$upload->validate()) {
            $this->json(['success' => false, 'message' => implode(', ', $upload->errors())], 422);
            return;
        }

        $path = $upload->save();
        if (!$path) {
            $this->json(['success' => false, 'message' => 'Upload failed'], 500);
            return;
        }

        $db = Database::getInstance();
        $docId = $db->insert('member_documents', [
            'member_id'          => $id,
            'document_type'      => $request->post('document_type', 'other'),
            'document_name'      => $request->post('document_name', $upload->originalName()),
            'file_path'          => $path,
            'file_original_name' => $upload->originalName(),
            'file_size_bytes'    => $upload->size(),
            'mime_type'          => $upload->mimeType(),
            'expiry_date'        => $request->post('expiry_date') ?: null,
            'uploaded_by'        => Auth::id(),
        ]);

        $this->json(['success' => true, 'message' => 'Document uploaded', 'id' => $docId]);
    }

    public function verifyDocument(Request $request): void
    {
        $docId = (int) $request->routeParam('docId');
        $db = Database::getInstance();
        $db->update('member_documents', [
            'is_verified'  => 1,
            'verified_by'  => Auth::id(),
            'verified_at'  => date('Y-m-d H:i:s'),
        ], 'id = ?', [$docId]);

        $this->json(['success' => true, 'message' => 'Document verified']);
    }

    public function deleteDocument(Request $request): void
    {
        $docId = (int) $request->routeParam('docId');
        $db = Database::getInstance();
        $db->update('member_documents', [
            'is_deleted' => 1,
            'deleted_at' => date('Y-m-d H:i:s'),
        ], 'id = ?', [$docId]);

        $this->json(['success' => true, 'message' => 'Document deleted']);
    }
}