<?php
require __DIR__ . '/bootstrap.php';

use App\Core\Auth;
use App\Core\Csrf;
use App\Core\Database;
use App\Core\Response;
use App\Core\Security;
use App\Services\UploadService;

$user = Auth::requireUser();
$pdo = Database::connection();
$input = request_input();
$method = $_SERVER['REQUEST_METHOD'];
$action = $input['action'] ?? 'profile';

function pyrk_member_role_id(PDO $pdo): int
{
    $stmt = $pdo->query('SELECT id FROM roles WHERE name = "member" LIMIT 1');
    $id = $stmt->fetchColumn();
    if (!$id) Response::error('Member role is missing from database.');
    return (int)$id;
}

function pyrk_member_system_user_options(PDO $pdo): array
{
    $stmt = $pdo->query('SELECT u.id, u.role_id, r.name AS role_name, u.full_name, u.email, u.mobile, u.profile_photo_path, u.status,
            CASE WHEN mp.user_id IS NULL THEN 0 ELSE 1 END AS has_member_profile
        FROM users u
        JOIN roles r ON r.id = u.role_id
        LEFT JOIN member_profiles mp ON mp.user_id = u.id
        WHERE r.name <> "member"
        ORDER BY u.full_name ASC');
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function pyrk_linkable_system_user(PDO $pdo, int $userId): ?array
{
    if ($userId <= 0) return null;

    $stmt = $pdo->prepare('SELECT u.*, r.name AS role_name,
            CASE WHEN mp.user_id IS NULL THEN 0 ELSE 1 END AS has_member_profile
        FROM users u
        JOIN roles r ON r.id = u.role_id
        LEFT JOIN member_profiles mp ON mp.user_id = u.id
        WHERE u.id = ? AND r.name <> "member"
        LIMIT 1');
    $stmt->execute([$userId]);
    $row = $stmt->fetch(PDO::FETCH_ASSOC);

    return $row ?: null;
}


function pyrk_member_table_exists(PDO $pdo, string $table): bool
{
    static $cache = [];
    if (array_key_exists($table, $cache)) return $cache[$table];

    try {
        $stmt = $pdo->prepare('SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = ?');
        $stmt->execute([$table]);
        return $cache[$table] = ((int)$stmt->fetchColumn() > 0);
    } catch (Throwable) {
        return $cache[$table] = false;
    }
}

function pyrk_member_column_exists(PDO $pdo, string $table, string $column): bool
{
    static $cache = [];
    $key = $table . '.' . $column;
    if (array_key_exists($key, $cache)) return $cache[$key];

    try {
        $stmt = $pdo->prepare('SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = ? AND column_name = ?');
        $stmt->execute([$table, $column]);
        return $cache[$key] = ((int)$stmt->fetchColumn() > 0);
    } catch (Throwable) {
        return $cache[$key] = false;
    }
}

function pyrk_member_merge_group_members(PDO $pdo, int $fromUserId, int $toUserId): void
{
    if (!pyrk_member_table_exists($pdo, 'member_group_members')) return;

    try {
        $pdo->prepare('INSERT IGNORE INTO member_group_members (group_id, user_id, added_by, created_at)
            SELECT group_id, ?, added_by, COALESCE(created_at, NOW())
            FROM member_group_members
            WHERE user_id = ?')->execute([$toUserId, $fromUserId]);
        $pdo->prepare('DELETE FROM member_group_members WHERE user_id = ?')->execute([$fromUserId]);
    } catch (Throwable) {
        // Do not block the main link action; the normal group sync runs after this.
    }
}

function pyrk_member_merge_user_references(PDO $pdo, int $fromUserId, int $toUserId): void
{
    if ($fromUserId <= 0 || $toUserId <= 0 || $fromUserId === $toUserId) return;

    pyrk_member_merge_group_members($pdo, $fromUserId, $toUserId);

    try {
        $stmt = $pdo->prepare('SELECT table_name
            FROM information_schema.columns
            WHERE table_schema = DATABASE()
              AND column_name = "user_id"
            ORDER BY table_name ASC');
        $stmt->execute();
        $tables = $stmt->fetchAll(PDO::FETCH_COLUMN);
    } catch (Throwable) {
        $tables = [];
    }

    $skip = [
        'users' => true,
        'user_permissions' => true,
        'member_profiles' => true,
        'member_group_members' => true,
    ];

    foreach ($tables as $table) {
        $table = (string)$table;
        if ($table === '' || isset($skip[$table])) continue;
        if (!preg_match('/^[A-Za-z0-9_]+$/', $table)) continue;

        try {
            // UPDATE IGNORE avoids breaking when the target system user already has a duplicate unique row.
            $pdo->prepare("UPDATE IGNORE `{$table}` SET user_id = ? WHERE user_id = ?")->execute([$toUserId, $fromUserId]);
            // Remove remaining duplicates/remainders so deleting the old member account does not fail on FKs.
            $pdo->prepare("DELETE FROM `{$table}` WHERE user_id = ?")->execute([$fromUserId]);
        } catch (Throwable) {
            // Older custom tables may have strict constraints; continue safely.
        }
    }
}

function pyrk_member_link_existing_member_to_system_user(PDO $pdo, int $fromMemberId, int $toSystemUserId): void
{
    if ($fromMemberId <= 0 || $toSystemUserId <= 0 || $fromMemberId === $toSystemUserId) return;

    pyrk_member_merge_user_references($pdo, $fromMemberId, $toSystemUserId);

    // Move the member profile itself last, after related history has been moved.
    if (pyrk_member_table_exists($pdo, 'member_profiles')) {
        $pdo->prepare('UPDATE member_profiles SET user_id = ?, updated_at = NOW() WHERE user_id = ?')->execute([$toSystemUserId, $fromMemberId]);
    }

    try {
        $pdo->prepare('DELETE FROM user_permissions WHERE user_id = ?')->execute([$fromMemberId]);
    } catch (Throwable) {}

    $pdo->prepare('DELETE FROM users WHERE id = ?')->execute([$fromMemberId]);
}



function pyrk_member_permission_catalog_for_api(): array
{
    $catalog = [];
    foreach (Auth::permissionCatalog() as $groupKey => $group) {
        $items = [];
        foreach (($group['items'] ?? []) as $key => $label) {
            $items[] = ['key' => $key, 'label' => $label];
        }
        $catalog[] = [
            'key' => $groupKey,
            'label' => $group['label'] ?? $groupKey,
            'items' => $items,
        ];
    }
    return $catalog;
}

function pyrk_member_permission_table_ready(PDO $pdo): bool
{
    try {
        $pdo->query('SELECT 1 FROM user_permissions LIMIT 1');
        return true;
    } catch (Throwable) {
        return false;
    }
}

function pyrk_member_selected_permission_keys(PDO $pdo, int $userId, string $roleName): array
{
    if (!pyrk_member_permission_table_ready($pdo)) {
        return Auth::defaultPermissionKeysForRole($roleName);
    }

    $countStmt = $pdo->prepare('SELECT COUNT(*) FROM user_permissions WHERE user_id = ?');
    $countStmt->execute([$userId]);
    $explicitCount = (int)$countStmt->fetchColumn();

    if ($explicitCount <= 0) {
        return Auth::defaultPermissionKeysForRole($roleName);
    }

    $stmt = $pdo->prepare('SELECT permission_key FROM user_permissions WHERE user_id = ? AND granted = 1 ORDER BY permission_key ASC');
    $stmt->execute([$userId]);
    return array_values(array_map('strval', $stmt->fetchAll(PDO::FETCH_COLUMN)));
}

function pyrk_member_replace_user_permissions(PDO $pdo, int $userId, array $permissions): void
{
    if (!pyrk_member_permission_table_ready($pdo)) {
        throw new RuntimeException('Permissions table is missing. Please run the user permissions migration first.');
    }

    $allowed = Auth::allPermissionKeys();
    $selected = array_values(array_unique(array_filter(array_map('strval', $permissions), static fn($p) => in_array($p, $allowed, true))));
    $selectedMap = array_flip($selected);

    $pdo->prepare('DELETE FROM user_permissions WHERE user_id = ?')->execute([$userId]);

    $stmt = $pdo->prepare('INSERT INTO user_permissions (user_id, permission_key, granted, created_at, updated_at) VALUES (?, ?, ?, NOW(), NOW())');
    foreach ($allowed as $permission) {
        $stmt->execute([$userId, $permission, isset($selectedMap[$permission]) ? 1 : 0]);
    }
}

function pyrk_member_permissions_from_input(array $input): ?array
{
    if (!array_key_exists('permissions', $input)) {
        return null;
    }

    $permissions = $input['permissions'];
    if (is_string($permissions)) {
        $decoded = json_decode($permissions, true);
        if (is_array($decoded)) {
            $permissions = $decoded;
        } else {
            $permissions = array_filter(array_map('trim', explode(',', $permissions)));
        }
    }

    if (!is_array($permissions)) {
        return [];
    }

    return $permissions;
}

function pyrk_member_system_role_options(PDO $pdo): array
{
    $stmt = $pdo->query('SELECT id, name FROM roles WHERE name <> "member" ORDER BY FIELD(name, "super_admin", "event_manager", "walk_leader"), name ASC');
    $roles = $stmt->fetchAll(PDO::FETCH_ASSOC);
    foreach ($roles as &$role) {
        $role['default_permissions'] = Auth::defaultPermissionKeysForRole((string)$role['name']);
    }
    unset($role);
    return $roles;
}

function pyrk_member_staff_role_by_id(PDO $pdo, int $roleId): ?array
{
    if ($roleId <= 0) return null;
    $stmt = $pdo->prepare('SELECT id, name FROM roles WHERE id = ? AND name <> "member" LIMIT 1');
    $stmt->execute([$roleId]);
    $role = $stmt->fetch(PDO::FETCH_ASSOC);
    return $role ?: null;
}

function pyrk_member_can_manage_system_privileges(?array $admin): bool
{
    return $admin && (Auth::hasPermission('users.manage', $admin) || (($admin['role_name'] ?? '') === 'super_admin'));
}

function pyrk_member_apply_system_privileges(PDO $pdo, int $userId, array $input, array $admin): array
{
    if (!array_key_exists('system_access_enabled', $input) && !array_key_exists('system_role_id', $input) && !array_key_exists('permissions', $input)) {
        return ['changed' => false];
    }

    if (!pyrk_member_can_manage_system_privileges($admin)) {
        Response::error('You do not have permission to grant system/user privileges to members.', 403);
    }

    $enabled = !empty($input['system_access_enabled']);
    $memberRoleId = pyrk_member_role_id($pdo);

    if (!$enabled) {
        $pdo->prepare('UPDATE users SET role_id = ?, updated_at = NOW() WHERE id = ?')->execute([$memberRoleId, $userId]);
        try {
            $pdo->prepare('DELETE FROM user_permissions WHERE user_id = ?')->execute([$userId]);
        } catch (Throwable) {}
        return ['changed' => true, 'system_access' => false, 'role_name' => 'member'];
    }

    $roleId = (int)($input['system_role_id'] ?? 0);
    $role = pyrk_member_staff_role_by_id($pdo, $roleId);
    if (!$role) {
        Response::error('Choose the system role to give this member user privileges.');
    }

    $permissions = pyrk_member_permissions_from_input($input);
    if ($permissions === null) {
        $permissions = Auth::defaultPermissionKeysForRole((string)$role['name']);
    }

    $pdo->prepare('UPDATE users SET role_id = ?, updated_at = NOW() WHERE id = ?')->execute([$roleId, $userId]);
    pyrk_member_replace_user_permissions($pdo, $userId, $permissions);

    return [
        'changed' => true,
        'system_access' => true,
        'role_id' => $roleId,
        'role_name' => (string)$role['name'],
        'permissions' => $permissions,
    ];
}


function pyrk_generate_member_qr_token(PDO $pdo): string
{
    do {
        $token = 'PYRK-MEMBER-' . strtoupper(bin2hex(random_bytes(8)));
        $stmt = $pdo->prepare('SELECT id FROM users WHERE qr_token = ? LIMIT 1');
        $stmt->execute([$token]);
    } while ($stmt->fetch());
    return $token;
}

function pyrk_avatar_from_request(): string
{
    if (empty($_FILES['profile_photo']) || (int)($_FILES['profile_photo']['error'] ?? UPLOAD_ERR_NO_FILE) === UPLOAD_ERR_NO_FILE) {
        return '';
    }
    return (new UploadService())->storeAvatar($_FILES['profile_photo']);
}


function pyrk_normalize_member_date(?string $value): ?string
{
    $value = trim((string)$value);
    if ($value === '') return null;
    if (preg_match('/^(\d{2})\/(\d{2})\/(\d{4})$/', $value, $m)) {
        if (!checkdate((int)$m[2], (int)$m[1], (int)$m[3])) Response::error('Date of birth must be valid dd/mm/yyyy.');
        return $m[3] . '-' . $m[2] . '-' . $m[1];
    }
    if (preg_match('/^(\d{4})-(\d{2})-(\d{2})$/', $value, $m)) {
        if (!checkdate((int)$m[2], (int)$m[3], (int)$m[1])) Response::error('Date of birth must be valid.');
        return $value;
    }
    Response::error('Date of birth must be dd/mm/yyyy.');
}





function pyrk_member_group_ids_from_input(array $input): array
{
    $groups = $input['group_ids'] ?? $input['groups'] ?? [];
    if (is_string($groups)) {
        $decoded = json_decode($groups, true);
        if (is_array($decoded)) {
            $groups = $decoded;
        } else {
            $groups = array_filter(array_map('trim', explode(',', $groups)));
        }
    }
    if (!is_array($groups)) {
        return [];
    }
    return array_values(array_unique(array_filter(array_map('intval', $groups))));
}

function pyrk_member_groups(PDO $pdo, int $memberId): array
{
    try {
        $stmt = $pdo->prepare('SELECT g.id, g.group_name, g.description, g.status, (SELECT COUNT(*) FROM member_group_members mgm2 WHERE mgm2.group_id = g.id) AS member_count FROM member_group_members mgm JOIN member_groups g ON g.id = mgm.group_id WHERE mgm.user_id = ? ORDER BY g.group_name ASC');
        $stmt->execute([$memberId]);
        return $stmt->fetchAll();
    } catch (Throwable) {
        return [];
    }
}

function pyrk_available_member_groups(PDO $pdo): array
{
    try {
        return $pdo->query('SELECT g.id, g.group_name, g.description, g.status, (SELECT COUNT(*) FROM member_group_members mgm WHERE mgm.group_id = g.id) AS member_count FROM member_groups g ORDER BY g.status ASC, g.group_name ASC')->fetchAll();
    } catch (Throwable) {
        return [];
    }
}

function pyrk_sync_member_groups(PDO $pdo, int $memberId, array $groupIds, int $adminId): void
{
    $groupIds = array_values(array_unique(array_filter(array_map('intval', $groupIds))));
    $pdo->prepare('DELETE FROM member_group_members WHERE user_id = ?')->execute([$memberId]);

    if (!$groupIds) {
        return;
    }

    $validStmt = $pdo->prepare('SELECT id FROM member_groups WHERE id IN (' . implode(',', array_fill(0, count($groupIds), '?')) . ')');
    $validStmt->execute($groupIds);
    $validGroupIds = array_values(array_map('intval', $validStmt->fetchAll(PDO::FETCH_COLUMN)));

    if (!$validGroupIds) {
        return;
    }

    $insert = $pdo->prepare('INSERT INTO member_group_members (group_id, user_id, created_by, created_at) VALUES (?, ?, ?, NOW())');
    foreach ($validGroupIds as $groupId) {
        $insert->execute([$groupId, $memberId, $adminId]);
    }
}


function pyrk_effective_loyalty_points(PDO $pdo, int $userId, $cachedPoints = 0): array
{
    $cached = max(0, (int)($cachedPoints ?? 0));
    try {
        $stmt = $pdo->prepare('SELECT COUNT(*) AS cnt, COALESCE(SUM(points), 0) AS balance FROM loyalty_transactions WHERE user_id = ?');
        $stmt->execute([$userId]);
        $row = $stmt->fetch() ?: ['cnt' => 0, 'balance' => 0];
        $ledgerCount = (int)($row['cnt'] ?? 0);
        $ledgerBalance = max(0, (int)($row['balance'] ?? 0));

        // If there is a loyalty ledger, it is the safest source of truth for demos and claims.
        // Keep member_profiles.loyalty_points synchronized so every page shows the same balance.
        $effective = $ledgerCount > 0 ? $ledgerBalance : $cached;
        if ($effective !== $cached) {
            $sync = $pdo->prepare('UPDATE member_profiles SET loyalty_points = ?, updated_at = NOW() WHERE user_id = ?');
            $sync->execute([$effective, $userId]);
        }
        return ['effective' => $effective, 'cached' => $cached, 'ledger' => $ledgerBalance, 'ledger_count' => $ledgerCount];
    } catch (Throwable $e) {
        return ['effective' => $cached, 'cached' => $cached, 'ledger' => null, 'ledger_count' => 0, 'error' => $e->getMessage()];
    }
}

function pyrk_apply_loyalty_balance(PDO $pdo, ?array $profile): ?array
{
    if (!$profile || empty($profile['id'])) return $profile;
    $balance = pyrk_effective_loyalty_points($pdo, (int)$profile['id'], $profile['loyalty_points'] ?? 0);
    $profile['loyalty_points'] = $balance['effective'];
    $profile['loyalty_points_effective'] = $balance['effective'];
    $profile['loyalty_points_cached'] = $balance['cached'];
    $profile['loyalty_points_ledger'] = $balance['ledger'];
    $profile['loyalty_ledger_count'] = $balance['ledger_count'];
    return $profile;
}


function pyrk_split_person_name(?string $fullName): array
{
    $fullName = trim(preg_replace('/\s+/', ' ', (string)$fullName));
    if ($fullName === '') {
        return ['first_name' => '', 'last_name' => '', 'full_name' => ''];
    }

    $parts = preg_split('/\s+/', $fullName);
    $first = array_shift($parts) ?: '';
    $last = trim(implode(' ', $parts));

    return [
        'first_name' => $first,
        'last_name' => $last,
        'full_name' => trim($first . ' ' . $last),
    ];
}

function pyrk_person_name_from_input(array $input): array
{
    $first = trim(preg_replace('/\s+/', ' ', (string)($input['first_name'] ?? '')));
    $last = trim(preg_replace('/\s+/', ' ', (string)($input['last_name'] ?? '')));
    $full = trim(preg_replace('/\s+/', ' ', (string)($input['full_name'] ?? '')));

    if ($first === '' && $last === '' && $full !== '') {
        $split = pyrk_split_person_name($full);
        $first = $split['first_name'];
        $last = $split['last_name'];
    }

    if ($first === '') Response::error('First Name is required.');
    if ($last === '') Response::error('Last Name is required.');

    return [
        'first_name' => $first,
        'last_name' => $last,
        'full_name' => trim($first . ' ' . $last),
    ];
}

function pyrk_attach_person_name_parts(?array $row): ?array
{
    if (!$row) return $row;
    $split = pyrk_split_person_name($row['full_name'] ?? '');
    $row['first_name'] = $row['first_name'] ?? $split['first_name'];
    $row['last_name'] = $row['last_name'] ?? $split['last_name'];
    $row['display_name'] = trim(($row['first_name'] ?? '') . ' ' . ($row['last_name'] ?? '')) ?: ($row['full_name'] ?? '');
    return $row;
}


function pyrk_member_select_sql(): string
{
    return 'SELECT u.id, u.role_id, r.name AS role_name, CASE WHEN r.name = "member" THEN 0 ELSE 1 END AS is_system_user, u.full_name, u.email, u.mobile, u.profile_photo_path, u.can_edit_own_profile, u.qr_token, u.status, u.failed_login_count, u.locked_until, u.last_login_at, u.last_login_ip, u.blocked_at, u.blocked_reason, u.created_at, u.updated_at, mp.date_of_birth, mp.gender, mp.emergency_contact_name, mp.emergency_contact_mobile, mp.medical_conditions, mp.photo_consent, mp.total_walks, mp.total_kilometers, mp.loyalty_points, TIMESTAMPDIFF(YEAR, mp.date_of_birth, CURDATE()) AS age, (SELECT COUNT(*) FROM walk_registrations wr WHERE wr.user_id = u.id) AS registrations_count, (SELECT COUNT(*) FROM walk_photos wp WHERE wp.user_id = u.id) AS photos_count, (SELECT COUNT(*) FROM user_badges ub WHERE ub.user_id = u.id) AS badges_count FROM users u JOIN roles r ON r.id = u.role_id LEFT JOIN member_profiles mp ON mp.user_id = u.id';
}

if ($method === 'GET' && $action === 'profile') {
    $stmt = $pdo->prepare(pyrk_member_select_sql() . ' WHERE u.id = ? LIMIT 1');
    $stmt->execute([(int)$user['id']]);
    $profile = pyrk_apply_loyalty_balance($pdo, $stmt->fetch() ?: null);
    $profile = pyrk_attach_person_name_parts($profile);
    if (!$profile) Response::error('Member profile not found for this user.', 404);

    $badges = $pdo->prepare('SELECT b.badge_name, b.description, b.badge_icon, ub.earned_at FROM user_badges ub JOIN badges b ON b.id = ub.badge_id WHERE ub.user_id = ? ORDER BY ub.earned_at DESC');
    $badges->execute([(int)$user['id']]);

    $history = $pdo->prepare('SELECT w.title, w.walk_date, r.route_name, a.status, a.verified_km, a.check_in_time, a.finish_time FROM attendance a JOIN walks w ON w.id = a.walk_id LEFT JOIN routes r ON r.id = w.route_id WHERE a.user_id = ? ORDER BY w.walk_date DESC LIMIT 30');
    $history->execute([(int)$user['id']]);

    $nextWalk = $pdo->prepare('SELECT w.*, r.route_name, r.total_km FROM walk_registrations wr JOIN walks w ON w.id = wr.walk_id JOIN routes r ON r.id = w.route_id WHERE wr.user_id = ? AND wr.registration_status IN ("registered","approved") AND w.walk_date >= CURDATE() ORDER BY w.walk_date, w.start_time LIMIT 1');
    $nextWalk->execute([(int)$user['id']]);

    $photos = $pdo->prepare('SELECT wp.id, wp.walk_id, wp.title, wp.caption, wp.status, wp.votes_count, wp.uploaded_at, w.title AS walk_title, w.walk_date, r.route_name FROM walk_photos wp JOIN walks w ON w.id = wp.walk_id LEFT JOIN routes r ON r.id = w.route_id WHERE wp.user_id = ? ORDER BY wp.uploaded_at DESC, wp.id DESC LIMIT 30');
    $photos->execute([(int)$user['id']]);

    $transactions = $pdo->prepare('SELECT lt.points, lt.transaction_type, lt.description, lt.created_at, w.title AS walk_title FROM loyalty_transactions lt LEFT JOIN walks w ON w.id = lt.walk_id WHERE lt.user_id = ? ORDER BY lt.created_at DESC, lt.id DESC LIMIT 50');
    $transactions->execute([(int)$user['id']]);

    $waivers = $pdo->prepare('SELECT ws.id, ws.walk_id, ws.signed_name, ws.signed_at, w.title AS walk_title, w.walk_date, r.route_name, wt.title AS waiver_title, wt.version AS waiver_version FROM waiver_signatures ws JOIN walks w ON w.id = ws.walk_id LEFT JOIN routes r ON r.id = w.route_id LEFT JOIN waiver_templates wt ON wt.id = ws.waiver_template_id WHERE ws.user_id = ? AND ws.accepted_terms = 1 ORDER BY ws.signed_at DESC, ws.id DESC LIMIT 30');
    $waivers->execute([(int)$user['id']]);

    Response::ok([
        'profile' => $profile,
        'member_groups' => pyrk_member_groups($pdo, (int)$user['id']),
        'badges' => $badges->fetchAll(),
        'history' => $history->fetchAll(),
        'photos' => $photos->fetchAll(),
        'points_ledger' => $transactions->fetchAll(),
        'waivers' => $waivers->fetchAll(),
        'next_walk' => $nextWalk->fetch() ?: null,
    ]);
}

if ($method === 'GET' && $action === 'detail') {
    Auth::requireRole(['super_admin','event_manager','walk_leader']);
    $id = (int)($input['id'] ?? 0);
    if (!$id) Response::error('Missing member id.');

    try {
        $stmt = $pdo->prepare(pyrk_member_select_sql() . ' WHERE u.id = ? AND (r.name = "member" OR mp.user_id IS NOT NULL) LIMIT 1');
        $stmt->execute([$id]);
        $member = pyrk_apply_loyalty_balance($pdo, $stmt->fetch() ?: null);
        $member = pyrk_attach_person_name_parts($member);
        if (!$member) Response::error('Member not found.', 404);
        $member['permissions'] = pyrk_member_selected_permission_keys($pdo, (int)$member['id'], (string)$member['role_name']);

        $walks = $pdo->prepare('SELECT wr.id AS registration_id, wr.registration_status, wr.registered_at, w.id AS walk_id, w.title AS walk_title, w.walk_date, w.start_time, w.expected_finish_time, w.status AS walk_status, r.id AS route_id, r.route_name, r.total_km, a.status AS attendance_status, a.check_in_time, a.finish_time, a.verified_km FROM walk_registrations wr JOIN walks w ON w.id = wr.walk_id JOIN routes r ON r.id = w.route_id LEFT JOIN attendance a ON a.walk_id = wr.walk_id AND a.user_id = wr.user_id WHERE wr.user_id = ? ORDER BY w.walk_date DESC, w.start_time DESC LIMIT 80');
        $walks->execute([$id]);

        $photos = $pdo->prepare('SELECT wp.id, wp.walk_id, wp.title, wp.caption, wp.file_path, wp.thumbnail_path, wp.status, wp.votes_count, wp.uploaded_at, w.title AS walk_title, w.walk_date, r.route_name FROM walk_photos wp JOIN walks w ON w.id = wp.walk_id JOIN routes r ON r.id = w.route_id WHERE wp.user_id = ? ORDER BY wp.uploaded_at DESC, wp.id DESC LIMIT 80');
        $photos->execute([$id]);

        $badges = $pdo->prepare('SELECT b.id AS badge_id, b.badge_name, b.description, b.badge_icon, ub.walk_id, ub.earned_at FROM user_badges ub JOIN badges b ON b.id = ub.badge_id WHERE ub.user_id = ? ORDER BY ub.earned_at DESC LIMIT 80');
        $badges->execute([$id]);

        $transactions = $pdo->prepare('SELECT lt.points, lt.transaction_type, lt.description, lt.created_at, w.title AS walk_title FROM loyalty_transactions lt LEFT JOIN walks w ON w.id = lt.walk_id WHERE lt.user_id = ? ORDER BY lt.created_at DESC, lt.id DESC LIMIT 50');
        $transactions->execute([$id]);

        $waivers = $pdo->prepare('SELECT ws.id, ws.walk_id, ws.waiver_template_id, ws.signed_name, ws.accepted_terms, ws.ip_address, ws.device_info, ws.signed_at, ws.created_at, w.title AS walk_title, w.walk_date, w.start_time, w.expected_finish_time, w.status AS walk_status, r.route_name, r.total_km, wt.title AS waiver_title, wt.version AS waiver_version FROM waiver_signatures ws JOIN walks w ON w.id = ws.walk_id LEFT JOIN routes r ON r.id = w.route_id LEFT JOIN waiver_templates wt ON wt.id = ws.waiver_template_id WHERE ws.user_id = ? AND ws.accepted_terms = 1 ORDER BY ws.signed_at DESC, ws.id DESC LIMIT 80');
        $waivers->execute([$id]);

        Response::ok([
            'member' => $member,
            'member_groups' => pyrk_member_groups($pdo, $id),
            'available_groups' => pyrk_available_member_groups($pdo),
            'walks' => $walks->fetchAll(),
            'photos' => $photos->fetchAll(),
            'badges' => $badges->fetchAll(),
            'transactions' => $transactions->fetchAll(),
            'waivers' => $waivers->fetchAll(),
            'roles' => pyrk_member_system_role_options($pdo),
            'permissions_catalog' => pyrk_member_permission_catalog_for_api(),
            'can_manage_system_privileges' => pyrk_member_can_manage_system_privileges($user),
        ]);
    } catch (Throwable $e) {
        Response::error('Could not load member profile: ' . $e->getMessage(), 500);
    }
}

if ($method === 'GET' && $action === 'system_user_options') {
    if (!Auth::hasPermission('members.view', $user) && !Auth::hasPermission('members.manage', $user)) Response::error('You do not have permission to view system users.', 403);
    Response::ok(['system_users' => pyrk_member_system_user_options($pdo)]);
}

if ($method === 'GET' && ($action === 'admin_list' || $action === 'list')) {
    if (!Auth::hasPermission('members.view', $user) && !Auth::hasPermission('members.manage', $user) && !Auth::hasPermission('members.delete', $user)) Response::error('You do not have permission to view members.', 403);
    $stmt = $pdo->query(pyrk_member_select_sql() . ' WHERE (r.name = "member" OR mp.user_id IS NOT NULL) ORDER BY u.created_at DESC, u.full_name ASC');
    $members = $stmt->fetchAll(PDO::FETCH_ASSOC);
    foreach ($members as &$row) {
        $row = pyrk_attach_person_name_parts($row);
    }
    unset($row);
    Response::ok([
        'members' => $members,
        'roles' => pyrk_member_system_role_options($pdo),
        'permissions_catalog' => pyrk_member_permission_catalog_for_api(),
        'can_manage_system_privileges' => pyrk_member_can_manage_system_privileges($user),
    ]);
}

if ($method === 'POST' && ($action === 'create_member' || $action === 'create_user')) {
    $admin = Auth::requireUser();
    if (!Auth::hasPermission('members.manage', $admin)) Response::error('You do not have permission to create members.', 403);
    Csrf::validateFromRequest();

    $personName = pyrk_person_name_from_input($input);

    $email = strtolower(trim((string)$input['email']));
    if ($email === '') Response::error('Missing field: email');
    if (!filter_var($email, FILTER_VALIDATE_EMAIL)) Response::error('Invalid email address.');

    $systemAccess = !empty($input['system_access_enabled']);
    $roleId = pyrk_member_role_id($pdo);
    $roleNameForDefaults = 'member';
    if ($systemAccess) {
        if (!pyrk_member_can_manage_system_privileges($admin)) {
            Response::error('You do not have permission to create members with system/user privileges.', 403);
        }
        $role = pyrk_member_staff_role_by_id($pdo, (int)($input['system_role_id'] ?? 0));
        if (!$role) Response::error('Choose the system role to give this member user privileges.');
        $roleId = (int)$role['id'];
        $roleNameForDefaults = (string)$role['name'];
    }

    $password = (string)($input['password'] ?? 'password');
    if (strlen($password) < 6) Response::error('Password must be at least 6 characters.');
    $avatar = pyrk_avatar_from_request();

    $pdo->beginTransaction();
    try {
        $canEditOwnProfile = !array_key_exists('can_edit_own_profile', $input) || !empty($input['can_edit_own_profile']) ? 1 : 0;
        $stmt = $pdo->prepare('INSERT INTO users (role_id, full_name, email, mobile, profile_photo_path, can_edit_own_profile, qr_token, password_hash, status, email_verified_at, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW(), NOW())');
        $stmt->execute([
            $roleId,
            $personName['full_name'],
            $email,
            trim((string)($input['mobile'] ?? '')),
            $avatar ?: null,
            $canEditOwnProfile,
            $systemAccess ? pyrk_generate_staff_qr_token($pdo) : pyrk_generate_member_qr_token($pdo),
            password_hash($password, PASSWORD_DEFAULT),
            $input['status'] ?? 'active',
        ]);
        $newUserId = (int)$pdo->lastInsertId();

        $profile = $pdo->prepare('INSERT INTO member_profiles (user_id, date_of_birth, gender, emergency_contact_name, emergency_contact_mobile, medical_conditions, photo_consent, total_walks, total_kilometers, loyalty_points, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, 0, 0.00, 0, NOW(), NOW())');
        $profile->execute([
            $newUserId,
            pyrk_normalize_member_date($input['date_of_birth'] ?? ''),
            !empty($input['gender']) ? $input['gender'] : null,
            trim((string)($input['emergency_contact_name'] ?? '')),
            trim((string)($input['emergency_contact_mobile'] ?? '')),
            trim((string)($input['medical_conditions'] ?? '')),
            !empty($input['photo_consent']) ? 1 : 0,
        ]);

        pyrk_sync_member_groups($pdo, $newUserId, pyrk_member_group_ids_from_input($input), (int)$admin['id']);

        if ($systemAccess) {
            $permissions = pyrk_member_permissions_from_input($input);
            if ($permissions === null) $permissions = Auth::defaultPermissionKeysForRole($roleNameForDefaults);
            pyrk_member_replace_user_permissions($pdo, $newUserId, $permissions);
        }

        $pdo->commit();
        Security::audit((int)$admin['id'], $systemAccess ? 'member_created_with_system_privileges' : 'member_created', 'users', $newUserId, [
            'email' => $email,
            'role_id' => $roleId,
            'groups' => pyrk_member_group_ids_from_input($input),
            'can_edit_own_profile' => $canEditOwnProfile,
        ]);
        Response::ok(['member_id' => $newUserId, 'system_access' => $systemAccess], $systemAccess ? 'Member created with user privileges.' : 'Member created.');
    } catch (Throwable $e) {
        if ($pdo->inTransaction()) $pdo->rollBack();
        if (str_contains($e->getMessage(), 'Duplicate')) Response::error('This email already exists.');
        Response::error('Could not create member: ' . $e->getMessage());
    }
}

if ($method === 'POST' && ($action === 'update_member' || $action === 'update_user')) {
    $admin = Auth::requireUser();
    if (!Auth::hasPermission('members.manage', $admin)) Response::error('You do not have permission to update members.', 403);
    Csrf::validateFromRequest();
    $id = (int)($input['id'] ?? 0);
    if (!$id) Response::error('Missing member id.');
    foreach (['email'] as $field) {
        if (trim((string)($input[$field] ?? '')) === '') Response::error("Missing field: {$field}");
    }
    $personName = pyrk_person_name_from_input($input);

    $existing = $pdo->prepare('SELECT u.id, u.can_edit_own_profile, r.name AS role_name FROM users u JOIN roles r ON r.id = u.role_id LEFT JOIN member_profiles mp ON mp.user_id = u.id WHERE u.id = ? AND (r.name = "member" OR mp.user_id IS NOT NULL) LIMIT 1');
    $existing->execute([$id]);
    $existingMember = $existing->fetch(PDO::FETCH_ASSOC);
    if (!$existingMember) Response::error('Member not found.', 404);

    $email = strtolower(trim((string)$input['email']));
    if (!filter_var($email, FILTER_VALIDATE_EMAIL)) Response::error('Invalid email address.');
    if (!empty($input['password']) && strlen((string)$input['password']) < 6) Response::error('Password must be at least 6 characters.');
    $avatar = pyrk_avatar_from_request();

    $pdo->beginTransaction();
    try {
        $canEditOwnProfile = array_key_exists('can_edit_own_profile', $input)
            ? (!empty($input['can_edit_own_profile']) ? 1 : 0)
            : (int)($existingMember['can_edit_own_profile'] ?? 1);
        $stmt = $pdo->prepare('UPDATE users SET full_name = ?, email = ?, mobile = ?, status = ?, can_edit_own_profile = ?, updated_at = NOW() WHERE id = ?');
        $stmt->execute([$personName['full_name'], $email, trim((string)($input['mobile'] ?? '')), $input['status'] ?? 'active', $canEditOwnProfile, $id]);
        if ($avatar) {
            $pdo->prepare('UPDATE users SET profile_photo_path = ?, updated_at = NOW() WHERE id = ?')->execute([$avatar, $id]);
        }
        if (!empty($input['password'])) {
            $pdo->prepare('UPDATE users SET password_hash = ?, updated_at = NOW() WHERE id = ?')->execute([password_hash((string)$input['password'], PASSWORD_DEFAULT), $id]);
        }

        $profile = $pdo->prepare('INSERT INTO member_profiles (user_id, date_of_birth, gender, emergency_contact_name, emergency_contact_mobile, medical_conditions, photo_consent, total_walks, total_kilometers, loyalty_points, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, 0, 0.00, 0, NOW(), NOW()) ON DUPLICATE KEY UPDATE date_of_birth = VALUES(date_of_birth), gender = VALUES(gender), emergency_contact_name = VALUES(emergency_contact_name), emergency_contact_mobile = VALUES(emergency_contact_mobile), medical_conditions = VALUES(medical_conditions), photo_consent = VALUES(photo_consent), updated_at = NOW()');
        $profile->execute([
            $id,
            pyrk_normalize_member_date($input['date_of_birth'] ?? ''),
            !empty($input['gender']) ? $input['gender'] : null,
            trim((string)($input['emergency_contact_name'] ?? '')),
            trim((string)($input['emergency_contact_mobile'] ?? '')),
            trim((string)($input['medical_conditions'] ?? '')),
            !empty($input['photo_consent']) ? 1 : 0,
        ]);

        pyrk_sync_member_groups($pdo, $id, pyrk_member_group_ids_from_input($input), (int)$admin['id']);
        $privilegeResult = pyrk_member_apply_system_privileges($pdo, $id, $input, $admin);

        $pdo->commit();
        Security::audit((int)$admin['id'], !empty($privilegeResult['changed']) ? 'member_privileges_updated' : 'member_updated', 'users', $id, [
            'email' => $email,
            'groups' => pyrk_member_group_ids_from_input($input),
            'system_privileges' => $privilegeResult,
            'can_edit_own_profile' => $canEditOwnProfile,
        ]);
        Response::ok(['member_id' => $id, 'system_privileges' => $privilegeResult], !empty($privilegeResult['changed']) ? 'Member and user privileges updated.' : 'Member updated.');
    } catch (Throwable $e) {
        if ($pdo->inTransaction()) $pdo->rollBack();
        if (str_contains($e->getMessage(), 'Duplicate')) Response::error('This email already exists.');
        Response::error('Could not update member: ' . $e->getMessage());
    }
}



if ($method === 'POST' && $action === 'bulk_activate_members') {
    $admin = Auth::requireUser();
    if (!Auth::hasPermission('members.manage', $admin)) Response::error('You do not have permission to manage members.', 403);
    Csrf::validateFromRequest();

    $rawIds = $input['member_ids'] ?? $input['ids'] ?? [];
    if (is_string($rawIds)) {
        $decoded = json_decode($rawIds, true);
        $rawIds = is_array($decoded) ? $decoded : explode(',', $rawIds);
    }
    if (!is_array($rawIds)) $rawIds = [];

    $ids = array_values(array_unique(array_filter(array_map('intval', $rawIds), static fn($id) => $id > 0)));
    if (!$ids) Response::error('Select at least one inactive member to activate.');
    if (count($ids) > 500) Response::error('For safety, please activate up to 500 members at a time.');

    $placeholders = implode(',', array_fill(0, count($ids), '?'));
    $stmt = $pdo->prepare('SELECT u.id, u.full_name, u.email, u.status
        FROM users u
        JOIN roles r ON r.id = u.role_id
        LEFT JOIN member_profiles mp ON mp.user_id = u.id
        WHERE u.id IN (' . $placeholders . ')
          AND (r.name = "member" OR mp.user_id IS NOT NULL)
        ORDER BY u.full_name ASC');
    $stmt->execute($ids);
    $members = $stmt->fetchAll(PDO::FETCH_ASSOC);

    if (!$members) Response::error('No matching members were found.');

    $inactiveIds = array_values(array_map('intval', array_column(array_filter($members, static fn($m) => ($m['status'] ?? '') === 'inactive'), 'id')));
    if (!$inactiveIds) Response::ok(['updated' => 0, 'skipped' => count($members)], 'No inactive members were selected. Blocked and already-active members were not changed.');

    $inactivePlaceholders = implode(',', array_fill(0, count($inactiveIds), '?'));
    $before = array_values(array_filter($members, static fn($m) => in_array((int)$m['id'], $inactiveIds, true)));

    $pdo->beginTransaction();
    try {
        $update = $pdo->prepare('UPDATE users
            SET status = "active", locked_until = NULL, failed_login_count = 0, blocked_at = NULL, blocked_by = NULL, blocked_reason = NULL, updated_at = NOW()
            WHERE id IN (' . $inactivePlaceholders . ') AND status = "inactive"');
        $update->execute($inactiveIds);
        $updated = $update->rowCount();
        Security::audit((int)$admin['id'], 'members_bulk_activated', 'users', null, ['member_ids' => $inactiveIds, 'updated' => $updated], $before);
        $pdo->commit();
    } catch (Throwable $e) {
        if ($pdo->inTransaction()) $pdo->rollBack();
        Response::error('Could not activate selected members: ' . $e->getMessage(), 500);
    }

    $skipped = max(0, count($members) - (int)$updated);
    $message = $updated === 1
        ? '1 inactive member was activated.'
        : $updated . ' inactive members were activated.';
    if ($skipped > 0) $message .= ' ' . $skipped . ' selected member(s) were already active or blocked and were not changed.';
    Response::ok(['updated' => (int)$updated, 'skipped' => $skipped], $message);
}

if ($method === 'POST' && $action === 'block_member') {
    $admin = Auth::requireUser();
    if (!Auth::hasPermission('members.manage', $admin)) Response::error('You do not have permission to manage member security.', 403);
    Csrf::validateFromRequest();
    $id = (int)($input['id'] ?? $input['user_id'] ?? 0);
    $reason = trim((string)($input['reason'] ?? 'Blocked by admin'));
    if (!$id) Response::error('Missing member id.');
    $existing = $pdo->prepare('SELECT u.*, r.name AS role_name FROM users u JOIN roles r ON r.id = u.role_id LEFT JOIN member_profiles mp ON mp.user_id = u.id WHERE u.id = ? AND (r.name = "member" OR mp.user_id IS NOT NULL) LIMIT 1');
    $existing->execute([$id]);
    $before = $existing->fetch();
    if (!$before) Response::error('Member not found.', 404);
    $pdo->prepare('UPDATE users SET status = "blocked", locked_until = NULL, failed_login_count = 0, blocked_at = NOW(), blocked_by = ?, blocked_reason = ?, updated_at = NOW() WHERE id = ?')->execute([(int)$admin['id'], $reason, $id]);
    Security::audit((int)$admin['id'], 'member_blocked', 'users', $id, ['reason' => $reason], $before);
    Response::ok([], 'Member blocked.');
}

if ($method === 'POST' && $action === 'unblock_member') {
    $admin = Auth::requireUser();
    if (!Auth::hasPermission('members.manage', $admin)) Response::error('You do not have permission to manage member security.', 403);
    Csrf::validateFromRequest();
    $id = (int)($input['id'] ?? $input['user_id'] ?? 0);
    if (!$id) Response::error('Missing member id.');
    $existing = $pdo->prepare('SELECT u.*, r.name AS role_name FROM users u JOIN roles r ON r.id = u.role_id LEFT JOIN member_profiles mp ON mp.user_id = u.id WHERE u.id = ? AND (r.name = "member" OR mp.user_id IS NOT NULL) LIMIT 1');
    $existing->execute([$id]);
    $before = $existing->fetch();
    if (!$before) Response::error('Member not found.', 404);
    $pdo->prepare('UPDATE users SET status = "active", locked_until = NULL, failed_login_count = 0, blocked_at = NULL, blocked_by = NULL, blocked_reason = NULL, updated_at = NOW() WHERE id = ?')->execute([$id]);
    Security::audit((int)$admin['id'], 'member_unblocked', 'users', $id, null, $before);
    Response::ok([], 'Member unblocked.');
}

if ($method === 'POST' && $action === 'reset_lock') {
    $admin = Auth::requireUser();
    if (!Auth::hasPermission('members.manage', $admin)) Response::error('You do not have permission to manage member security.', 403);
    Csrf::validateFromRequest();
    $id = (int)($input['id'] ?? $input['user_id'] ?? 0);
    if (!$id) Response::error('Missing member id.');
    $pdo->prepare('UPDATE users SET locked_until = NULL, failed_login_count = 0, updated_at = NOW() WHERE id = ?')->execute([$id]);
    Security::audit((int)$admin['id'], 'member_lock_reset', 'users', $id);
    Response::ok([], 'Member login lock reset.');
}


if ($method === 'POST' && ($action === 'delete_member' || $action === 'delete_user')) {
    $admin = Auth::requireUser();
    if (!Auth::hasPermission('members.delete', $admin)) Response::error('You do not have permission to delete members.', 403);
    Csrf::validateFromRequest();

    $id = (int)($input['id'] ?? $input['user_id'] ?? 0);
    if (!$id) Response::error('Missing member id.');
    if ($id === (int)$admin['id']) Response::error('You cannot delete your own account here.');

    $existing = $pdo->prepare('SELECT u.*, r.name AS role_name FROM users u JOIN roles r ON r.id = u.role_id LEFT JOIN member_profiles mp ON mp.user_id = u.id WHERE u.id = ? AND (r.name = "member" OR mp.user_id IS NOT NULL) LIMIT 1');
    $existing->execute([$id]);
    $before = $existing->fetch();
    if (!$before) Response::error('Member not found.', 404);

    try {
        $pdo->beginTransaction();
        // Most related member data has ON DELETE CASCADE. These cleanup statements make older upgraded DBs safer.
        foreach ([
            'member_group_members',
            'walk_invitations',
            'user_permissions'
        ] as $table) {
            try {
                if ($table === 'walk_invitations') {
                    $pdo->prepare('DELETE FROM walk_invitations WHERE user_id = ?')->execute([$id]);
                } else {
                    $pdo->prepare("DELETE FROM {$table} WHERE user_id = ?")->execute([$id]);
                }
            } catch (Throwable) {}
        }
        $pdo->prepare('DELETE FROM users WHERE id = ?')->execute([$id]);
        Security::audit((int)$admin['id'], 'member_deleted', 'users', $id, ['email' => $before['email'] ?? null], $before);
        $pdo->commit();
        Response::ok([], 'Member deleted.');
    } catch (Throwable $e) {
        if ($pdo->inTransaction()) $pdo->rollBack();
        Response::error('Could not delete member: ' . $e->getMessage());
    }
}

Response::error('Unsupported members action.', 404);
