<?php
/**
 * Velora Dating Platform — front controller
 * Single-entry router for auth, profile, discovery, matches, chat, premium, safety, settings, admin.
 */
require __DIR__ . '/../app/core/bootstrap.php';

$path = trim(parse_url($_SERVER['REQUEST_URI'] ?? '/', PHP_URL_PATH), '/');
$base = trim(parse_url($config['app_url'], PHP_URL_PATH), '/');
if ($base && str_starts_with($path, $base)) {
    $path = trim(substr($path, strlen($base)), '/');
}
$method = $_SERVER['REQUEST_METHOD'] ?? 'GET';

Csrf::verify($path);
$auth->refresh();

// Global visitor analytics (all cities/countries when geo available)
try { track_visit($path, $method); } catch (Throwable $e) {}

function view(string $file, array $vars = []): void
{
    global $db, $auth, $config, $method;
    extract(array_merge(['db' => $db, 'auth' => $auth, 'config' => $config, 'method' => $method], $vars), EXTR_SKIP);
    require __DIR__ . '/../app/views/' . $file;
    exit;
}

function post_only(): void
{
    global $method;
    if ($method !== 'POST') {
        http_response_code(405);
        exit('Method Not Allowed');
    }
}

function json_out(array $data, int $code = 200): void
{
    http_response_code($code);
    header('Content-Type: application/json; charset=utf-8');
    echo json_encode($data);
    exit;
}

function ensure_user_rows(int $uid): void
{
    global $db;
    $db->exec('INSERT IGNORE INTO user_profiles(user_id,city,relationship_goal,profile_completion) VALUES(?,"Nairobi","Long-term",20)', [$uid]);
    $db->exec('INSERT IGNORE INTO user_preferences(user_id,min_age,max_age,max_distance_km,show_gender) VALUES(?,18,99,50,"Everyone")', [$uid]);
    $db->exec('INSERT IGNORE INTO credit_wallets(user_id,balance) VALUES(?,0)', [$uid]);
    $db->exec('INSERT IGNORE INTO notification_preferences(user_id) VALUES(?)', [$uid]);
}

function are_matched(int $a, int $b): bool
{
    global $db;
    return (bool) $db->one(
        'SELECT id FROM matches WHERE (user_one_id=? AND user_two_id=?) OR (user_one_id=? AND user_two_id=?)',
        [$a, $b, $b, $a]
    );
}

function is_blocked(int $a, int $b): bool
{
    global $db;
    return (bool) $db->one(
        'SELECT id FROM blocks WHERE (user_id=? AND blocked_user_id=?) OR (user_id=? AND blocked_user_id=?)',
        [$a, $b, $b, $a]
    );
}


/* ---------- Visitor analytics (GPS / location) ---------- */
if ($path === 'analytics/geo' && $method === 'POST') {
    $raw = file_get_contents('php://input') ?: '';
    $data = json_decode($raw, true);
    if (!is_array($data)) {
        $data = $_POST;
    }
    $lat = isset($data['lat']) ? (float) $data['lat'] : null;
    $lng = isset($data['lng']) ? (float) $data['lng'] : null;
    $acc = isset($data['accuracy']) ? (float) $data['accuracy'] : null;
    $eventId = (int) ($_SESSION['last_visit_event_id'] ?? 0);
    if ($eventId < 1 && !empty($_SESSION['visitor_sid'])) {
        try {
            $row = $db->one('SELECT id FROM visitor_events WHERE session_key=? ORDER BY id DESC LIMIT 1', [$_SESSION['visitor_sid']]);
            $eventId = (int) ($row['id'] ?? 0);
        } catch (Throwable $e) {}
    }
    if ($lat !== null && $lng !== null && abs($lat) <= 90 && abs($lng) <= 180) {
        update_visit_geo($eventId, [
            'lat' => $lat,
            'lng' => $lng,
            'accuracy' => $acc,
            'city' => isset($data['city']) ? mb_substr((string)$data['city'], 0, 120) : null,
            'region' => isset($data['region']) ? mb_substr((string)$data['region'], 0, 120) : null,
            'country' => isset($data['country']) ? mb_substr((string)$data['country'], 0, 80) : null,
            'continent' => isset($data['continent']) ? mb_substr((string)$data['continent'], 0, 40) : null,
            'source' => 'gps',
        ]);
        // also stamp latest session visits without coords
        try {
            $db->exec(
                'UPDATE visitor_events SET latitude=?, longitude=?, accuracy_m=?, geo_source="gps"
                 WHERE session_key=? AND (latitude IS NULL OR geo_source="none") AND created_at > DATE_SUB(NOW(), INTERVAL 1 HOUR)',
                [$lat, $lng, $acc, visitor_session_key()]
            );
        } catch (Throwable $e) {}
    }
    json_out(['ok' => true]);
}

/* ---------- Health ---------- */
if ($path === 'health' && $method === 'GET') {
    try {
        $db->one('SELECT 1 AS ok');
        json_out(['ok' => true, 'app' => $config['app_name'], 'database' => 'connected', 'php' => PHP_VERSION]);
    } catch (Throwable $e) {
        json_out(['ok' => false, 'app' => $config['app_name'], 'database' => 'error', 'message' => 'Database connection failed'], 500);
    }
}

/* ---------- Home ---------- */
if ($path === '' || $path === 'home') {
    view('layouts/home.php');
}

/* ---------- Register ---------- */
if ($path === 'register' && $method === 'GET') {
    view('auth/register.php');
}
if ($path === 'register' && $method === 'POST') {
    $name = trim($_POST['name'] ?? '');
    $email = strtolower(trim($_POST['email'] ?? ''));
    $dob = $_POST['dob'] ?? '';
    $pass = $_POST['password'] ?? '';
    $confirm = $_POST['password_confirm'] ?? '';
    $adult = isset($_POST['adult']) && $_POST['adult'] !== '';
    // Preserve non-sensitive fields for redisplay
    $_SESSION['_old'] = ['name' => $name, 'email' => $email, 'dob' => $dob];
    if (!$adult) {
        flash('error', 'You must confirm you are 18 or older to join Velora.');
        redirect('register');
    }
    if (strlen($name) < 2) {
        flash('error', 'Please enter your name (at least 2 characters).');
        redirect('register');
    }
    if (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
        flash('error', 'Please enter a valid email address.');
        redirect('register');
    }
    if ($dob === '') {
        flash('error', 'Please enter your date of birth.');
        redirect('register');
    }
    try {
        $age = age_from_dob($dob);
    } catch (Throwable $e) {
        flash('error', 'Please enter a valid date of birth.');
        redirect('register');
    }
    if ($age < 18) {
        flash('error', 'You must be at least 18 years old to create an account.');
        redirect('register');
    }
    if (strlen($pass) < 8) {
        flash('error', 'Password must be at least 8 characters.');
        redirect('register');
    }
    if ($pass !== $confirm) {
        flash('error', 'Password and confirmation do not match.');
        redirect('register');
    }
    if ($db->one('SELECT id FROM users WHERE email=?', [$email])) {
        flash('error', 'An account already exists with that email.');
        redirect('register');
    }
    try {
        $trialHours = (int) (payment_settings()['trial_hours'] ?? 72);
        if ($trialHours < 1) { $trialHours = 72; }
        $db->exec(
            'INSERT INTO users(name,email,password_hash,dob,role,status,country,created_at,last_seen_at,trial_ends_at,is_verified_paid) VALUES(?,?,?,?,"user","active","Kenya",NOW(),NOW(),DATE_ADD(NOW(), INTERVAL ? HOUR),0)',
            [$name, $email, password_hash($pass, PASSWORD_DEFAULT), $dob, $trialHours]
        );
        $id = (int) $db->lastId();
        $db->exec('INSERT INTO user_profiles(user_id,city,profile_completion) VALUES(?,"Nairobi",20)', [$id]);
        $db->exec('INSERT INTO user_preferences(user_id) VALUES(?)', [$id]);
        $db->exec('INSERT INTO credit_wallets(user_id,balance) VALUES(?,0)', [$id]);
        $db->exec('INSERT INTO notification_preferences(user_id) VALUES(?)', [$id]);
    } catch (Throwable $e) {
        // Fallback without new columns if migration not run yet
        try {
            $db->exec(
                'INSERT INTO users(name,email,password_hash,dob,role,status,country,created_at,last_seen_at) VALUES(?,?,?,?,"user","active","Kenya",NOW(),NOW())',
                [$name, $email, password_hash($pass, PASSWORD_DEFAULT), $dob]
            );
            $id = (int) $db->lastId();
            $db->exec('INSERT INTO user_profiles(user_id,city,profile_completion) VALUES(?,"Nairobi",20)', [$id]);
            $db->exec('INSERT INTO user_preferences(user_id) VALUES(?)', [$id]);
            $db->exec('INSERT INTO credit_wallets(user_id,balance) VALUES(?,0)', [$id]);
            $db->exec('INSERT INTO notification_preferences(user_id) VALUES(?)', [$id]);
        } catch (Throwable $e2) {
            flash('error', 'Could not create your account. Please try again or contact support.');
            redirect('register');
        }
    }
    unset($_SESSION['_old']);
    $auth->login($email, $pass);
    notify($id, 'system', 'Welcome to Velora', 'You have 72 hours of free full access. After that, Monthly Access (KES 500) is required.');
    flash('success', 'Welcome! Enjoy 72 hours free access, then activate Monthly Access (KES 500) to keep exploring.');
    redirect('profile');
}

/* ---------- Login / Logout ---------- */
if ($path === 'login' && $method === 'GET') {
    view('auth/login.php');
}
if ($path === 'login' && $method === 'POST') {
    if ($auth->login(strtolower(trim($_POST['email'] ?? '')), $_POST['password'] ?? '')) {
        $lu = current_user();
        if ($lu && ($lu['role'] ?? '') === 'admin') {
            try { log_admin('Admin login', 'session', (int) $lu['id'], ['ip' => $_SERVER['REMOTE_ADDR'] ?? '']); } catch (Throwable $e) {}
        }
        redirect('discovery');
    }
    flash('error', 'Invalid login or inactive account.');
    redirect('login');
}
if ($path === 'logout') {
    $auth->logout();
    redirect('home');
}

/* ---------- Profile (own) ---------- */
if ($path === 'profile' && $method === 'GET') {
    require_login();
    $u = current_user();
    ensure_user_rows((int) $u['id']);
    $p = $db->one('SELECT * FROM user_profiles WHERE user_id=?', [$u['id']]) ?: [
        'city' => 'Nairobi', 'bio' => '', 'relationship_goal' => 'Long-term',
        'gender' => '', 'orientation' => '', 'pronouns' => '', 'profile_completion' => 20,
    ];
    $prefs = $db->one('SELECT * FROM user_preferences WHERE user_id=?', [$u['id']]) ?: [
        'min_age' => 18, 'max_age' => 99, 'max_distance_km' => 50, 'show_gender' => 'Everyone', 'dealbreakers' => 0,
    ];
    $interests = $db->all('SELECT * FROM interests WHERE active=1 ORDER BY name');
    $selected = $db->all('SELECT interest_id FROM user_interests WHERE user_id=?', [$u['id']]);
    $sel = array_map('intval', array_column($selected, 'interest_id'));
    $photos = $db->all('SELECT * FROM user_photos WHERE user_id=? ORDER BY is_primary DESC, id DESC', [$u['id']]);
    $prompts = $db->all(
        'SELECT pp.*, upa.answer FROM profile_prompts pp
         LEFT JOIN user_prompt_answers upa ON upa.prompt_id=pp.id AND upa.user_id=?
         WHERE pp.active=1 ORDER BY pp.id',
        [$u['id']]
    );
    view('profile/profile.php', compact('u', 'p', 'prefs', 'interests', 'sel', 'photos', 'prompts'));
}

if ($path === 'profile/save' && $method === 'POST') {
    require_login();
    $u = current_user();
    ensure_user_rows((int) $u['id']);
    $bio = trim($_POST['bio'] ?? '');
    $city = trim($_POST['city'] ?? '');
    $goal = trim($_POST['relationship_goal'] ?? '');
    $gender = trim($_POST['gender'] ?? '');
    $orientation = trim($_POST['orientation'] ?? '');
    $pronouns = trim($_POST['pronouns'] ?? '');
    $hobbies = trim($_POST['hobbies'] ?? '');
    $likesText = trim($_POST['likes_text'] ?? '');
    $customInterests = trim($_POST['custom_interests'] ?? '');
    $locationLabel = trim($_POST['location_label'] ?? '');
    $showPhone = isset($_POST['show_phone']) ? 1 : 0;
    $phone = trim($_POST['phone'] ?? '');
    $completion = 40;
    if ($bio !== '') { $completion += 15; }
    if ($gender !== '') { $completion += 10; }
    if ($city !== '') { $completion += 10; }
    if ($hobbies !== '' || $likesText !== '') { $completion += 10; }
    $photoCount = (int) ($db->one('SELECT COUNT(*) c FROM user_photos WHERE user_id=?', [$u['id']])['c'] ?? 0);
    if ($photoCount > 0) { $completion += 15; }
    try {
        $db->exec(
            'UPDATE user_profiles SET bio=?, city=?, relationship_goal=?, gender=?, orientation=?, pronouns=?, hobbies=?, likes_text=?, custom_interests=?, location_label=?, show_phone=?, profile_completion=?, updated_at=NOW() WHERE user_id=?',
            [$bio, $city, $goal, $gender, $orientation, $pronouns, mb_substr($hobbies, 0, 500), mb_substr($likesText, 0, 500), mb_substr($customInterests, 0, 500), mb_substr($locationLabel, 0, 190), $showPhone, min(100, $completion), $u['id']]
        );
    } catch (Throwable $e) {
        $db->exec(
            'UPDATE user_profiles SET bio=?, city=?, relationship_goal=?, gender=?, orientation=?, pronouns=?, profile_completion=?, updated_at=NOW() WHERE user_id=?',
            [$bio, $city, $goal, $gender, $orientation, $pronouns, min(100, $completion), $u['id']]
        );
    }
    if ($phone !== '') {
        $db->exec('UPDATE users SET phone=? WHERE id=?', [mb_substr($phone, 0, 30), $u['id']]);
    }
    $country = trim($_POST['country'] ?? '');
    if ($country !== '') {
        $db->exec('UPDATE users SET country=? WHERE id=?', [mb_substr($country, 0, 80), $u['id']]);
    }
    $db->exec(
        'UPDATE user_preferences SET min_age=?, max_age=?, max_distance_km=?, show_gender=?, dealbreakers=? WHERE user_id=?',
        [
            max(18, (int) ($_POST['min_age'] ?? 18)),
            min(99, (int) ($_POST['max_age'] ?? 99)),
            max(1, (int) ($_POST['max_distance_km'] ?? 50)),
            trim($_POST['show_gender'] ?? 'Everyone'),
            isset($_POST['dealbreakers']) ? 1 : 0,
            $u['id'],
        ]
    );
    $db->exec('DELETE FROM user_interests WHERE user_id=?', [$u['id']]);
    foreach (($_POST['interests'] ?? []) as $iid) {
        $db->exec('INSERT IGNORE INTO user_interests(user_id, interest_id) VALUES(?,?)', [$u['id'], (int) $iid]);
    }
    foreach (($_POST['prompt_answer'] ?? []) as $pid => $ans) {
        $ans = trim((string) $ans);
        if ($ans !== '') {
            $db->exec(
                'INSERT INTO user_prompt_answers(user_id, prompt_id, answer) VALUES(?,?,?) ON DUPLICATE KEY UPDATE answer=VALUES(answer)',
                [$u['id'], (int) $pid, mb_substr($ans, 0, 500)]
            );
        }
    }
    $auth->refresh();
    flash('success', 'Profile and discovery preferences saved.');
    redirect('profile');
}

if ($path === 'profile/photo' && $method === 'POST') {
    require_login();
    $u = current_user();
    if (empty($_FILES['photo']['tmp_name']) || !is_uploaded_file($_FILES['photo']['tmp_name'])) {
        flash('error', 'Choose a clear photo of yourself first.');
        redirect('profile');
    }
    $f = $_FILES['photo'];
    if (($f['error'] ?? UPLOAD_ERR_OK) !== UPLOAD_ERR_OK) {
        flash('error', 'Upload failed. Try a smaller JPG/PNG/WebP (max 8MB).');
        redirect('profile');
    }
    $allowed = ['image/jpeg' => 'jpg', 'image/png' => 'png', 'image/webp' => 'webp'];
    $mime = null;
    if (class_exists('finfo')) {
        $mime = (new finfo(FILEINFO_MIME_TYPE))->file($f['tmp_name']);
    } elseif (function_exists('mime_content_type')) {
        $mime = mime_content_type($f['tmp_name']);
    } else {
        $ext = strtolower(pathinfo($f['name'], PATHINFO_EXTENSION));
        $map = ['jpg' => 'image/jpeg', 'jpeg' => 'image/jpeg', 'png' => 'image/png', 'webp' => 'image/webp'];
        $mime = $map[$ext] ?? '';
    }
    if (!isset($allowed[$mime])) {
        flash('error', 'Only JPG, PNG or WebP photos are allowed.');
        redirect('profile');
    }
    if ($f['size'] > 8 * 1024 * 1024) {
        flash('error', 'Photo must be 8MB or smaller.');
        redirect('profile');
    }
    // Real image check + minimum size (discourage tiny icons / logos)
    $info = @getimagesize($f['tmp_name']);
    if (!$info || empty($info[0]) || empty($info[1])) {
        flash('error', 'That file is not a valid photo.');
        redirect('profile');
    }
    $w = (int) $info[0];
    $h = (int) $info[1];
    if ($w < 400 || $h < 400) {
        flash('error', 'Use a clearer personal photo (at least 400×400 pixels). Face photos work best.');
        redirect('profile');
    }
    $count = (int) ($db->one('SELECT COUNT(*) c FROM user_photos WHERE user_id=?', [$u['id']])['c'] ?? 0);
    if ($count >= 6) {
        flash('error', 'You can upload up to 6 photos. Delete one to add another.');
        redirect('profile');
    }
    $name = bin2hex(random_bytes(12)) . '.' . $allowed[$mime];
    $dir = __DIR__ . '/uploads/profiles';
    if (!is_dir($dir)) {
        mkdir($dir, 0755, true);
    }
    $dest = $dir . '/' . $name;
    // Re-encode JPEG to strip EXIF GPS when possible (privacy)
    $saved = false;
    if ($mime === 'image/jpeg' && function_exists('imagecreatefromjpeg')) {
        $im = @imagecreatefromjpeg($f['tmp_name']);
        if ($im) {
            // Cap longest side at 1600px for storage
            $maxSide = 1600;
            if ($w > $maxSide || $h > $maxSide) {
                $scale = $maxSide / max($w, $h);
                $nw = (int) round($w * $scale);
                $nh = (int) round($h * $scale);
                $resized = imagecreatetruecolor($nw, $nh);
                imagecopyresampled($resized, $im, 0, 0, 0, 0, $nw, $nh, $w, $h);
                imagedestroy($im);
                $im = $resized;
            }
            $saved = imagejpeg($im, $dest, 85);
            imagedestroy($im);
        }
    }
    if (!$saved) {
        if (!move_uploaded_file($f['tmp_name'], $dest)) {
            flash('error', 'Could not save the photo. Check folder permissions on public/uploads/profiles.');
            redirect('profile');
        }
    }
    $db->exec(
        'INSERT INTO user_photos(user_id, path, is_primary, moderation_status) VALUES(?,?,?,"approved")',
        [$u['id'], 'uploads/profiles/' . $name, $count ? 0 : 1]
    );
    $db->exec('UPDATE user_profiles SET profile_completion=LEAST(100, profile_completion+15) WHERE user_id=?', [$u['id']]);
    flash('success', 'Photo uploaded. Prefer clear photos of you (face + shoulders) — not objects or logos.');
    redirect('profile');
}

if ($path === 'profile/photo/delete' && $method === 'POST') {
    require_login();
    $u = current_user();
    $id = (int) ($_POST['photo_id'] ?? 0);
    $ph = $db->one('SELECT * FROM user_photos WHERE id=? AND user_id=?', [$id, $u['id']]);
    if ($ph) {
        $db->exec('DELETE FROM user_photos WHERE id=?', [$id]);
        @unlink(__DIR__ . '/' . ltrim($ph['path'], '/'));
        $next = $db->one('SELECT id FROM user_photos WHERE user_id=? ORDER BY id LIMIT 1', [$u['id']]);
        if ($next) {
            $db->exec('UPDATE user_photos SET is_primary=1 WHERE id=?', [$next['id']]);
        }
    }
    redirect('profile');
}


/* ---------- View another member profile (before / after match) ---------- */
if ($path === 'profile/view' && $method === 'GET') {
    require_login();
    $u = current_user();
    $id = (int) ($_GET['id'] ?? $_GET['user'] ?? 0);
    if (!$id || $id === (int) $u['id']) {
        redirect('profile');
    }
    if (is_blocked((int) $u['id'], $id)) {
        flash('error', 'This profile is not available.');
        redirect('discovery');
    }
    try {
        $member = $db->one(
            'SELECT u.id, u.name, u.dob, u.country, u.last_seen_at, u.status, u.phone, u.is_verified_paid, u.paid_until,
                    p.bio, p.city, p.relationship_goal, p.gender, p.orientation, p.pronouns, p.profile_completion, p.visibility,
                    p.hobbies, p.likes_text, p.custom_interests, p.location_label, p.show_phone
             FROM users u
             LEFT JOIN user_profiles p ON p.user_id = u.id
             WHERE u.id=? AND u.status="active" AND u.deleted_at IS NULL',
            [$id]
        );
    } catch (Throwable $e) {
        $member = $db->one(
            'SELECT u.id, u.name, u.dob, u.country, u.last_seen_at, u.status,
                    p.bio, p.city, p.relationship_goal, p.gender, p.orientation, p.pronouns, p.profile_completion, p.visibility
             FROM users u
             LEFT JOIN user_profiles p ON p.user_id = u.id
             WHERE u.id=? AND u.status="active" AND u.deleted_at IS NULL',
            [$id]
        );
    }
    if (!$member || (isset($member['visibility']) && (int) $member['visibility'] === 0 && !are_matched((int) $u['id'], $id))) {
        flash('error', 'Profile not found or hidden.');
        redirect('discovery');
    }
    $photos = $db->all(
        'SELECT * FROM user_photos WHERE user_id=? AND moderation_status="approved" ORDER BY is_primary DESC, id ASC',
        [$id]
    );
    $interestRows = $db->all(
        'SELECT i.id, i.name FROM user_interests ui JOIN interests i ON i.id=ui.interest_id WHERE ui.user_id=? ORDER BY i.name',
        [$id]
    );
    $myInterestIds = array_map('intval', array_column(
        $db->all('SELECT interest_id FROM user_interests WHERE user_id=?', [$u['id']]),
        'interest_id'
    ));
    $commonInterests = [];
    $otherInterests = [];
    foreach ($interestRows as $row) {
        if (in_array((int) $row['id'], $myInterestIds, true)) {
            $commonInterests[] = $row['name'];
        } else {
            $otherInterests[] = $row['name'];
        }
    }
    $prompts = $db->all(
        'SELECT pp.prompt, upa.answer FROM user_prompt_answers upa
         JOIN profile_prompts pp ON pp.id = upa.prompt_id
         WHERE upa.user_id=? AND TRIM(upa.answer) <> "" ORDER BY pp.id',
        [$id]
    );
    $matched = are_matched((int) $u['id'], $id);
    $liked = (bool) $db->one('SELECT id FROM likes WHERE user_id=? AND target_user_id=?', [$u['id'], $id]);
    $theyLiked = (bool) $db->one('SELECT id FROM likes WHERE user_id=? AND target_user_id=?', [$id, $u['id']]);
    $member['age'] = isset($member['dob']) ? age_from_dob($member['dob']) : null;
    $plan = user_plan($u['id']);
    view('profile/view.php', compact(
        'u', 'member', 'photos', 'commonInterests', 'otherInterests',
        'prompts', 'matched', 'liked', 'theyLiked', 'plan'
    ));
}

/* ---------- Discovery ---------- */
if ($path === 'discovery') {
    require_login();
    $u = current_user();
    ensure_user_rows((int) $u['id']);
    if (!has_active_access((int) $u['id'])) {
        flash('error', 'Your free trial has ended. Activate Monthly Access (KES ' . number_format(monthly_price_kes()) . ') to continue discovering and matching.');
        redirect('premium');
    }
    $prefs = $db->one('SELECT * FROM user_preferences WHERE user_id=?', [$u['id']]) ?: [
        'min_age' => 18, 'max_age' => 99, 'max_distance_km' => 50, 'show_gender' => 'Everyone',
    ];
    $min = max(18, (int) $prefs['min_age']);
    $max = min(99, (int) $prefs['max_age']);
    // Match rotation: only hide passes from the last 24 hours (older swipes can reappear)
    // Global discovery: all cities, countries, continents (no distance lock)
    $sql = 'SELECT u.id, u.name, u.dob, u.last_seen_at, u.country, p.bio, p.city, p.relationship_goal, p.gender, p.orientation, p.profile_completion,
            p.hobbies, p.likes_text, p.location_label, u.is_verified_paid, u.paid_until,
            GROUP_CONCAT(DISTINCT i.name ORDER BY i.name SEPARATOR ", ") AS interests
            FROM users u
            JOIN user_profiles p ON p.user_id = u.id
            LEFT JOIN user_interests ui ON ui.user_id = u.id
            LEFT JOIN interests i ON i.id = ui.interest_id
            WHERE u.id <> ?
              AND u.status = "active"
              AND u.deleted_at IS NULL
              AND p.visibility = 1
              AND (p.incognito = 0 OR p.incognito IS NULL)
              AND u.dob BETWEEN DATE_SUB(CURDATE(), INTERVAL ? YEAR) AND DATE_SUB(CURDATE(), INTERVAL ? YEAR)
              AND u.id NOT IN (SELECT target_user_id FROM likes WHERE user_id=?)
              AND u.id NOT IN (SELECT target_user_id FROM passes WHERE user_id=? AND created_at > DATE_SUB(NOW(), INTERVAL 24 HOUR))
              AND u.id NOT IN (SELECT blocked_user_id FROM blocks WHERE user_id=?)
              AND u.id NOT IN (SELECT user_id FROM blocks WHERE blocked_user_id=?)
            GROUP BY u.id
            ORDER BY (u.is_verified_paid = 1) DESC, RAND(), u.last_seen_at DESC
            LIMIT 60';
    try {
        $profiles = $db->all($sql, [$u['id'], $max, $min, $u['id'], $u['id'], $u['id'], $u['id']]);
    } catch (Throwable $e) {
        // Fallback without new profile columns
        $sql = 'SELECT u.id, u.name, u.dob, u.last_seen_at, p.bio, p.city, p.relationship_goal, p.gender, p.orientation, p.profile_completion,
                GROUP_CONCAT(DISTINCT i.name ORDER BY i.name SEPARATOR ", ") AS interests
                FROM users u
                JOIN user_profiles p ON p.user_id = u.id
                LEFT JOIN user_interests ui ON ui.user_id = u.id
                LEFT JOIN interests i ON i.id = ui.interest_id
                WHERE u.id <> ?
                  AND u.status = "active"
                  AND u.deleted_at IS NULL
                  AND p.visibility = 1
                  AND (p.incognito = 0 OR p.incognito IS NULL)
                  AND u.dob BETWEEN DATE_SUB(CURDATE(), INTERVAL ? YEAR) AND DATE_SUB(CURDATE(), INTERVAL ? YEAR)
                  AND u.id NOT IN (SELECT target_user_id FROM likes WHERE user_id=?)
                  AND u.id NOT IN (SELECT target_user_id FROM passes WHERE user_id=? AND created_at > DATE_SUB(NOW(), INTERVAL 24 HOUR))
                  AND u.id NOT IN (SELECT blocked_user_id FROM blocks WHERE user_id=?)
                  AND u.id NOT IN (SELECT user_id FROM blocks WHERE blocked_user_id=?)
                GROUP BY u.id
                ORDER BY p.profile_completion DESC, u.last_seen_at DESC, u.created_at DESC
                LIMIT 40';
        $profiles = $db->all($sql, [$u['id'], $max, $min, $u['id'], $u['id'], $u['id'], $u['id']]);
    }
    $myInterestIds = array_map('intval', array_column(
        $db->all('SELECT interest_id FROM user_interests WHERE user_id=?', [$u['id']]),
        'interest_id'
    ));
    foreach ($profiles as &$x) {
        $photoRows = [];
        try {
            $photoRows = $db->all(
                'SELECT path FROM user_photos WHERE user_id=? AND (moderation_status="approved" OR moderation_status IS NULL OR moderation_status="") ORDER BY is_primary DESC, id ASC LIMIT 6',
                [$x['id']]
            );
        } catch (Throwable $e) {
            $photoRows = $db->all(
                'SELECT path FROM user_photos WHERE user_id=? ORDER BY is_primary DESC, id ASC LIMIT 6',
                [$x['id']]
            );
        }
        $paths = [];
        foreach ($photoRows as $pr) {
            $path = $pr['path'] ?? '';
            if ($path === '') continue;
            // Prefer files that exist on disk
            $abs = __DIR__ . '/' . ltrim($path, '/');
            if (is_file($abs) || str_starts_with($path, 'http')) {
                $paths[] = $path;
            } else {
                $paths[] = $path; // still expose path; browser 404 is last resort
            }
        }
        $x['photos'] = $paths;
        $x['photo'] = $paths[0] ?? null;
        $x['photo_count'] = count($paths);
        $x['age'] = isset($x['dob']) ? age_from_dob($x['dob']) : null;
        $theirIds = array_map('intval', array_column(
            $db->all('SELECT interest_id FROM user_interests WHERE user_id=?', [$x['id']]),
            'interest_id'
        ));
        $x['common_interest_count'] = count(array_intersect($myInterestIds, $theirIds));
    }
    unset($x);
    usort($profiles, function ($a, $b) {
        $c = ($b['common_interest_count'] ?? 0) <=> ($a['common_interest_count'] ?? 0);
        if ($c !== 0) return $c;
        return ($b['profile_completion'] ?? 0) <=> ($a['profile_completion'] ?? 0);
    });
    $plan = user_plan($u['id']);
    view('discovery/index.php', compact('profiles', 'u', 'plan'));
}

if (in_array($path, ['discover/action', 'discover/like', 'discover/pass', 'discover/superlike'], true) && $method === 'POST') {
    require_login();
    $u = current_user();
    $target = (int) ($_POST['target_id'] ?? 0);
    $action = $_POST['action'] ?? ($path === 'discover/pass' ? 'pass' : ($path === 'discover/superlike' ? 'superlike' : 'like'));
    if (!$target || $target === (int) $u['id']) {
        redirect('discovery');
    }
    if (is_blocked((int) $u['id'], $target)) {
        flash('error', 'You cannot interact with this member.');
        redirect('discovery');
    }
    if (!has_active_access((int) $u['id'])) {
        flash('error', 'Your free trial has ended. Please activate Monthly Access to continue.');
        redirect('premium');
    }
    if ($action === 'pass') {
        $db->exec(
            'INSERT INTO passes(user_id, target_user_id, created_at) VALUES(?,?,NOW())
             ON DUPLICATE KEY UPDATE created_at = NOW()',
            [$u['id'], $target]
        );
    } elseif ($action === 'superlike') {
        if (!has_plan($u['id'], 'Gold')) {
            flash('error', 'Super Likes require Gold or VIP.');
            redirect('premium');
        }
        $db->exec('INSERT INTO super_likes(user_id, target_user_id) VALUES(?,?)', [$u['id'], $target]);
        $db->exec('INSERT IGNORE INTO likes(user_id, target_user_id) VALUES(?,?)', [$u['id'], $target]);
        notify($target, 'like', 'Someone sent you a Super Like', 'You have a new enhanced like.');
    } else {
        $db->exec('INSERT IGNORE INTO likes(user_id, target_user_id) VALUES(?,?)', [$u['id'], $target]);
        notify($target, 'like', 'New like', 'Someone liked your profile.');
    }
    $mutual = $db->one('SELECT id FROM likes WHERE user_id=? AND target_user_id=?', [$target, $u['id']]);
    if ($mutual) {
        $a = min((int) $u['id'], $target);
        $b = max((int) $u['id'], $target);
        $db->exec('INSERT IGNORE INTO matches(user_one_id, user_two_id) VALUES(?,?)', [$a, $b]);
        notify($target, 'match', 'New match', 'You have a new match. Start a conversation!');
        notify($u['id'], 'match', 'It is a match!', 'You matched with someone new.');
        flash('success', "It's a match! Open Matches to start chatting.");
    }
    redirect('discovery');
}

if ($path === 'discover/rewind' && $method === 'POST') {
    require_login();
    $u = current_user();
    if (!has_active_access((int) $u['id'])) {
        flash('error', 'Activate Monthly Access to keep discovering and use Rewind.');
        redirect('premium');
    }
    // Undo the most recent like OR pass (whichever is newer) so accidental swipes can be fixed
    $lastLike = $db->one('SELECT id, target_user_id, created_at, "like" AS kind FROM likes WHERE user_id=? ORDER BY created_at DESC, id DESC LIMIT 1', [$u['id']]);
    $lastPass = $db->one('SELECT id, target_user_id, created_at, "pass" AS kind FROM passes WHERE user_id=? ORDER BY created_at DESC, id DESC LIMIT 1', [$u['id']]);
    $pick = null;
    if ($lastLike && $lastPass) {
        $pick = (strtotime($lastLike['created_at']) >= strtotime($lastPass['created_at'])) ? $lastLike : $lastPass;
    } else {
        $pick = $lastLike ?: $lastPass;
    }
    if (!$pick) {
        flash('error', 'Nothing to rewind yet. Swipe first, then use Rewind if you change your mind.');
        redirect('discovery');
    }
    $target = (int) $pick['target_user_id'];
    if ($pick['kind'] === 'like') {
        $db->exec('DELETE FROM likes WHERE id=?', [$pick['id']]);
        $db->exec('DELETE FROM super_likes WHERE user_id=? AND target_user_id=?', [$u['id'], $target]);
        // Remove match only if we created it and there are no messages yet
        $a = min((int) $u['id'], $target);
        $b = max((int) $u['id'], $target);
        $match = $db->one('SELECT id FROM matches WHERE user_one_id=? AND user_two_id=?', [$a, $b]);
        if ($match) {
            $msgCount = (int) ($db->one(
                'SELECT COUNT(*) c FROM messages WHERE (sender_id=? AND receiver_id=?) OR (sender_id=? AND receiver_id=?)',
                [$u['id'], $target, $target, $u['id']]
            )['c'] ?? 0);
            if ($msgCount === 0) {
                $db->exec('DELETE FROM matches WHERE id=?', [$match['id']]);
            }
        }
        flash('success', 'Rewound your last like. That profile can show up again.');
    } else {
        $db->exec('DELETE FROM passes WHERE id=?', [$pick['id']]);
        flash('success', 'Rewound your last pass. You can decide again on that profile.');
    }
    redirect('discovery');
}

/* ---------- Matches ---------- */
if ($path === 'matches') {
    require_login();
    $u = current_user();
    if (!can_message((int) $u['id'])) {
        flash('error', 'Your inbox unlocks after Monthly Access payment. This keeps chats moderated and members genuine.');
        redirect('premium');
    }
    $matches = $db->all(
        'SELECT m.*,
            CASE WHEN m.user_one_id=? THEN u2.id ELSE u1.id END AS partner_id,
            CASE WHEN m.user_one_id=? THEN u2.name ELSE u1.name END AS partner_name,
            CASE WHEN m.user_one_id=? THEN p2.city ELSE p1.city END AS city,
            (SELECT COUNT(*) FROM messages msg
             WHERE msg.receiver_id=? AND msg.sender_id = (CASE WHEN m.user_one_id=? THEN u2.id ELSE u1.id END)
               AND msg.is_read=0) AS unread,
            (SELECT body FROM messages msg2
             WHERE (msg2.sender_id=? AND msg2.receiver_id = (CASE WHEN m.user_one_id=? THEN u2.id ELSE u1.id END))
                OR (msg2.receiver_id=? AND msg2.sender_id = (CASE WHEN m.user_one_id=? THEN u2.id ELSE u1.id END))
             ORDER BY msg2.created_at DESC LIMIT 1) AS last_message
         FROM matches m
         JOIN users u1 ON u1.id = m.user_one_id
         JOIN users u2 ON u2.id = m.user_two_id
         LEFT JOIN user_profiles p1 ON p1.user_id = u1.id
         LEFT JOIN user_profiles p2 ON p2.user_id = u2.id
         WHERE m.user_one_id=? OR m.user_two_id=?
         ORDER BY m.created_at DESC',
        [$u['id'], $u['id'], $u['id'], $u['id'], $u['id'], $u['id'], $u['id'], $u['id'], $u['id'], $u['id'], $u['id']]
    );
    foreach ($matches as &$m) {
        $ph = $db->one('SELECT path FROM user_photos WHERE user_id=? ORDER BY is_primary DESC, id DESC LIMIT 1', [$m['partner_id']]);
        $m['photo'] = $ph['path'] ?? null;
    }
    unset($m);
    view('matches/index.php', compact('matches'));
}

/* ---------- Chat ---------- */
if ($path === 'chat' && $method === 'GET') {
    require_login();
    $u = current_user();
    if (!can_message((int) $u['id'])) {
        flash('error', 'Messaging is for paid members only. Activate Monthly Access to unlock your inbox.');
        redirect('premium');
    }
    $partner = (int) ($_GET['user'] ?? 0);
    if (!$partner || $partner === (int) $u['id']) {
        flash('error', 'Choose a match to chat with.');
        redirect('matches');
    }
    if (!are_matched((int) $u['id'], $partner)) {
        flash('error', 'You can only chat with mutual matches.');
        redirect('matches');
    }
    if (is_blocked((int) $u['id'], $partner)) {
        flash('error', 'This conversation is blocked.');
        redirect('matches');
    }
    $partnerUser = $db->one(
        'SELECT u.id, u.name, u.dob, u.status, u.last_seen_at, p.bio, p.city, p.relationship_goal, p.gender
         FROM users u
         LEFT JOIN user_profiles p ON p.user_id = u.id
         WHERE u.id=? AND u.status="active" AND u.deleted_at IS NULL',
        [$partner]
    );
    if (!$partnerUser) {
        flash('error', 'Member not found or inactive.');
        redirect('matches');
    }
    $db->exec('UPDATE messages SET is_read=1 WHERE sender_id=? AND receiver_id=?', [$partner, $u['id']]);
    $db->exec('UPDATE users SET last_seen_at=NOW() WHERE id=?', [$u['id']]);
    // presence row
    try {
        $db->exec(
            'INSERT INTO chat_presence(user_id, peer_id, last_seen_at) VALUES(?,?,NOW())
             ON DUPLICATE KEY UPDATE last_seen_at=NOW()',
            [$u['id'], $partner]
        );
    } catch (Throwable $e) { /* table may not exist on old installs */ }
    $messages = $db->all(
        'SELECT * FROM messages WHERE (sender_id=? AND receiver_id=?) OR (sender_id=? AND receiver_id=?) ORDER BY created_at ASC, id ASC',
        [$u['id'], $partner, $partner, $u['id']]
    );
    $partnerPhoto = $db->one(
        'SELECT path FROM user_photos WHERE user_id=? ORDER BY is_primary DESC, id DESC LIMIT 1',
        [$partner]
    );
    $partnerUser['photo'] = $partnerPhoto['path'] ?? null;
    $online = false;
    if (!empty($partnerUser['last_seen_at'])) {
        $online = (time() - strtotime($partnerUser['last_seen_at'])) < 90;
    }
    $a = min((int) $u['id'], $partner);
    $b = max((int) $u['id'], $partner);
    $callRoom = 'velora-' . $a . '-' . $b;
    $jitsiUrl = 'https://meet.jit.si/' . rawurlencode($callRoom);
    $partnerProfile = $db->one('SELECT * FROM user_profiles WHERE user_id=?', [$partner]) ?: [];
    $ints = $db->all(
        'SELECT i.name FROM user_interests ui JOIN interests i ON i.id=ui.interest_id WHERE ui.user_id=?',
        [$partner]
    );
    $partnerProfile['interests'] = implode(', ', array_column($ints, 'name'));
    $icebreakers = [];
    if (!can_foreach($messages)) {
        $icebreakers = icebreaker_lines($u, $partnerUser, $partnerProfile);
    }
    if (!can_message((int) $u['id'])) {
        flash('error', 'Messaging is for paid members only. Activate Monthly Access to unlock your inbox.');
        redirect('premium');
    }
    view('chat/index.php', compact('partnerUser', 'messages', 'u', 'online', 'jitsiUrl', 'callRoom', 'icebreakers', 'partnerProfile'));
}

if ($path === 'chat/send' && $method === 'POST') {
    require_login();
    $u = current_user();
    if (!can_message((int) $u['id'])) {
        $ajax = isset($_POST['ajax']) || (isset($_SERVER['HTTP_X_REQUESTED_WITH']) && strtolower($_SERVER['HTTP_X_REQUESTED_WITH']) === 'xmlhttprequest');
        if ($ajax) { json_out(['ok' => false, 'error' => 'Messaging requires Monthly Access payment.'], 402); }
        flash('error', 'Messaging is for paid members only.');
        redirect('premium');
    }
    $to = (int) ($_POST['receiver_id'] ?? 0);
    $body = trim($_POST['body'] ?? '');
    $ajax = isset($_POST['ajax']) || (isset($_SERVER['HTTP_X_REQUESTED_WITH']) && strtolower($_SERVER['HTTP_X_REQUESTED_WITH']) === 'xmlhttprequest');
    if (!$to || $body === '') {
        if ($ajax) { json_out(['ok' => false, 'error' => 'Empty message'], 400); }
        redirect('matches');
    }
    if (!are_matched((int) $u['id'], $to) || is_blocked((int) $u['id'], $to)) {
        if ($ajax) { json_out(['ok' => false, 'error' => 'Not allowed'], 403); }
        flash('error', 'You can only message mutual matches.');
        redirect('matches');
    }
    $db->exec(
        'INSERT INTO messages(sender_id, receiver_id, body) VALUES(?,?,?)',
        [$u['id'], $to, mb_substr($body, 0, 2000)]
    );
    $msgId = (int) $db->lastId();
    notify($to, 'message', 'New message', 'You have a new message from ' . ($u['name'] ?? 'a match') . '.');
    if ($ajax) {
        $msg = $db->one('SELECT * FROM messages WHERE id=?', [$msgId]);
        json_out([
            'ok' => true,
            'message' => [
                'id' => (int) $msg['id'],
                'sender_id' => (int) $msg['sender_id'],
                'body' => $msg['body'],
                'is_read' => (int) $msg['is_read'],
                'created_at' => $msg['created_at'],
                'mine' => true,
            ],
        ]);
    }
    redirect('chat?user=' . $to);
}

if ($path === 'chat/poll' && $method === 'GET') {
    require_login();
    $u = current_user();
    if (!can_message((int) $u['id'])) {
        json_out(['ok' => false, 'messages' => [], 'error' => 'paid_required'], 402);
    }
    $partner = (int) ($_GET['user'] ?? 0);
    $after = (int) ($_GET['after'] ?? 0);
    if (!$partner || !are_matched((int) $u['id'], $partner)) {
        json_out(['ok' => false, 'messages' => []], 403);
    }
    $db->exec('UPDATE messages SET is_read=1 WHERE sender_id=? AND receiver_id=? AND is_read=0', [$partner, $u['id']]);
    $db->exec('UPDATE users SET last_seen_at=NOW() WHERE id=?', [$u['id']]);
    try {
        $db->exec(
            'INSERT INTO chat_presence(user_id, peer_id, last_seen_at) VALUES(?,?,NOW())
             ON DUPLICATE KEY UPDATE last_seen_at=NOW()',
            [$u['id'], $partner]
        );
    } catch (Throwable $e) {}
    $rows = $db->all(
        'SELECT id, sender_id, body, is_read, created_at FROM messages
         WHERE ((sender_id=? AND receiver_id=?) OR (sender_id=? AND receiver_id=?))
           AND id > ?
         ORDER BY id ASC LIMIT 100',
        [$u['id'], $partner, $partner, $u['id'], $after]
    );
    $out = [];
    foreach ($rows as $r) {
        $out[] = [
            'id' => (int) $r['id'],
            'sender_id' => (int) $r['sender_id'],
            'body' => $r['body'],
            'is_read' => (int) $r['is_read'],
            'created_at' => $r['created_at'],
            'mine' => ((int) $r['sender_id'] === (int) $u['id']),
        ];
    }
    $peer = $db->one('SELECT last_seen_at FROM users WHERE id=?', [$partner]);
    $online = $peer && $peer['last_seen_at'] && (time() - strtotime($peer['last_seen_at'])) < 90;
    $typing = false;
    try {
        $pres = $db->one(
            'SELECT typing_at FROM chat_presence WHERE user_id=? AND peer_id=?',
            [$partner, $u['id']]
        );
        if ($pres && !empty($pres['typing_at']) && (time() - strtotime($pres['typing_at'])) < 4) {
            $typing = true;
        }
    } catch (Throwable $e) {}
    json_out(['ok' => true, 'messages' => $out, 'online' => $online, 'typing' => $typing]);
}

if ($path === 'chat/typing' && $method === 'POST') {
    require_login();
    $u = current_user();
    if (!can_message((int) $u['id'])) {
        json_out(['ok' => false], 402);
    }
    $to = (int) ($_POST['peer_id'] ?? $_POST['user'] ?? 0);
    if ($to && are_matched((int) $u['id'], $to)) {
        try {
            $db->exec(
                'INSERT INTO chat_presence(user_id, peer_id, typing_at, last_seen_at) VALUES(?,?,NOW(),NOW())
                 ON DUPLICATE KEY UPDATE typing_at=NOW(), last_seen_at=NOW()',
                [$u['id'], $to]
            );
        } catch (Throwable $e) {}
    }
    json_out(['ok' => true]);
}

if ($path === 'chat/call' && $method === 'GET') {
    require_login();
    $u = current_user();
    if (!can_message((int) $u['id'])) {
        flash('error', 'Calls require Monthly Access.');
        redirect('premium');
    }
    $partner = (int) ($_GET['user'] ?? 0);
    if (!$partner || !are_matched((int) $u['id'], $partner) || is_blocked((int) $u['id'], $partner)) {
        flash('error', 'Video calls are only available with mutual matches.');
        redirect('matches');
    }
    $partnerUser = $db->one('SELECT id, name FROM users WHERE id=? AND status="active"', [$partner]);
    if (!$partnerUser) {
        redirect('matches');
    }
    $a = min((int) $u['id'], $partner);
    $b = max((int) $u['id'], $partner);
    $callRoom = 'velora-' . $a . '-' . $b;
    $jitsiUrl = 'https://meet.jit.si/' . rawurlencode($callRoom) . '#userInfo.displayName="' . rawurlencode($u['name'] ?? 'Velora') . '"';
    view('chat/call.php', compact('partnerUser', 'u', 'jitsiUrl', 'callRoom'));
}

if ($path === 'chat/unmatch' && $method === 'POST') {
    require_login();
    $u = current_user();
    $to = (int) ($_POST['user_id'] ?? 0);
    if ($to) {
        $db->exec('DELETE FROM matches WHERE (user_one_id=? AND user_two_id=?) OR (user_one_id=? AND user_two_id=?)', [$u['id'], $to, $to, $u['id']]);
        $db->exec('DELETE FROM likes WHERE (user_id=? AND target_user_id=?) OR (user_id=? AND target_user_id=?)', [$u['id'], $to, $to, $u['id']]);
        flash('success', 'Unmatched.');
    }
    redirect('matches');
}

if ($path === 'block' && $method === 'POST') {
    require_login();
    $u = current_user();
    $to = (int) ($_POST['user_id'] ?? 0);
    if ($to && $to !== (int) $u['id']) {
        $db->exec('INSERT IGNORE INTO blocks(user_id, blocked_user_id) VALUES(?,?)', [$u['id'], $to]);
        $db->exec('DELETE FROM matches WHERE (user_one_id=? AND user_two_id=?) OR (user_one_id=? AND user_two_id=?)', [$u['id'], $to, $to, $u['id']]);
        $db->exec('DELETE FROM likes WHERE (user_id=? AND target_user_id=?) OR (user_id=? AND target_user_id=?)', [$u['id'], $to, $to, $u['id']]);
        flash('success', 'Member blocked.');
    }
    redirect('matches');
}

if ($path === 'report' && $method === 'POST') {
    require_login();
    $u = current_user();
    $to = (int) ($_POST['user_id'] ?? 0);
    $reason = trim($_POST['reason'] ?? 'Other');
    $details = trim($_POST['details'] ?? '');
    if ($to && $to !== (int) $u['id']) {
        $db->exec(
            'INSERT INTO reports(reporter_id, reported_user_id, reason, details) VALUES(?,?,?,?)',
            [$u['id'], $to, mb_substr($reason, 0, 100), mb_substr($details, 0, 2000)]
        );
        flash('success', 'Report submitted. Our team will review it.');
    }
    redirect('matches');
}

/* ---------- Premium ---------- */
if ($path === 'premium') {
    require_login();
    $u = current_user();
    ensure_user_rows((int) $u['id']);
    $paySettings = payment_settings();
    $price = monthly_price_kes();
    $hasAccess = has_active_access((int) $u['id']);
    $isPaid = is_paid_member((int) $u['id']);
    $trialLeft = trial_hours_left((int) $u['id']);

    try {
        $plans = $db->all('SELECT * FROM subscription_plans WHERE active=1 ORDER BY sort_order');
    } catch (Throwable $e) {
        $plans = [];
    }
    if (!is_array($plans)) {
        $plans = [];
    }

    try {
        $current = user_plan($u['id']);
    } catch (Throwable $e) {
        $current = null;
    }
    if (!is_array($current)) {
        $current = ['id' => 0, 'name' => 'Free', 'price_kes' => 0, 'billing_period' => 'month'];
    }

    try {
        $sub = $db->one(
            'SELECT s.*, p.name AS plan_name FROM user_subscriptions s
             JOIN subscription_plans p ON p.id = s.plan_id
             WHERE s.user_id = ? AND s.status = "active" AND (s.ends_at IS NULL OR s.ends_at > NOW())
             ORDER BY s.ends_at DESC LIMIT 1',
            [$u['id']]
        );
    } catch (Throwable $e) {
        $sub = null;
    }

    try {
        $wallet = $db->one('SELECT balance FROM credit_wallets WHERE user_id=?', [$u['id']]);
    } catch (Throwable $e) {
        $wallet = null;
    }
    if (!is_array($wallet)) {
        $wallet = ['balance' => 0];
    }

    $tx = [];
    try {
        $rows = $db->all(
            'SELECT pt.id, pt.user_id, pt.plan_id, pt.amount, pt.currency, pt.provider,
                    pt.reference, pt.status, pt.created_at, pt.phone, pt.checkout_request_id,
                    sp.name AS plan_name
             FROM payment_transactions pt
             LEFT JOIN subscription_plans sp ON sp.id = pt.plan_id
             WHERE pt.user_id = ?
             ORDER BY pt.created_at DESC, pt.id DESC
             LIMIT 30',
            [$u['id']]
        );
        if (is_array($rows)) {
            $tx = $rows;
        }
    } catch (Throwable $e) {
        try {
            $rows = $db->all(
                'SELECT pt.*, sp.name AS plan_name FROM payment_transactions pt
                 LEFT JOIN subscription_plans sp ON sp.id = pt.plan_id
                 WHERE pt.user_id = ? ORDER BY pt.created_at DESC LIMIT 30',
                [$u['id']]
            );
            $tx = is_array($rows) ? $rows : [];
        } catch (Throwable $e2) {
            $tx = [];
        }
    }

    $userRow = $db->one('SELECT trial_ends_at, paid_until, is_verified_paid, phone FROM users WHERE id=?', [$u['id']]) ?: [];
    view('premium/index.php', compact(
        'plans', 'current', 'sub', 'wallet', 'u', 'tx', 'paySettings', 'price',
        'hasAccess', 'isPaid', 'trialLeft', 'userRow'
    ));
}

if ($path === 'premium/mpesa' && $method === 'POST') {
    require_login();
    $u = current_user();
    $phoneIn = trim($_POST['phone'] ?? '');
    $settings = payment_settings();
    $price = monthly_price_kes();
    $phone = MpesaService::normalizePhone($phoneIn);
    if (!$phone) {
        flash('error', 'Enter a valid Kenyan mobile number (e.g. 07XX XXX XXX or 2547...).');
        redirect('premium');
    }
    if (empty($settings['mpesa_enabled'])) {
        flash('error', 'M-Pesa payments are disabled. Enable them in Admin → Payment integrations.');
        redirect('premium');
    }
    $mpesa = new MpesaService($settings);
    if (!$mpesa->isConfigured()) {
        flash('error', 'M-Pesa is not fully configured. Admin must set Consumer Key, Secret, Shortcode and Passkey.');
        redirect('premium');
    }
    $ref = 'VL' . strtoupper(bin2hex(random_bytes(4)));
    $plan = $db->one('SELECT id FROM subscription_plans WHERE name="Monthly Access" AND active=1 LIMIT 1');
    $planId = $plan ? (int) $plan['id'] : null;

    $result = $mpesa->stkPush($phone, $price, $ref, 'Velora Monthly');
    if (!$result['ok']) {
        flash('error', $result['message'] ?? 'Could not start M-Pesa payment.');
        redirect('premium');
    }

    $checkout = $result['checkout_request_id'] ?? '';
    $merchant = $result['merchant_request_id'] ?? '';
    try {
        $db->exec(
            'INSERT INTO payment_transactions(user_id, plan_id, amount, currency, provider, reference, status, phone, checkout_request_id, merchant_request_id, metadata) VALUES(?,?,?,?,?,?,?,?,?,?,?)',
            [$u['id'], $planId, $price, 'KES', 'mpesa', $ref, 'pending', $phone, $checkout, $merchant, json_encode(['stk_init' => $result['raw'] ?? []])]
        );
    } catch (Throwable $e) {
        $db->exec(
            'INSERT INTO payment_transactions(user_id, plan_id, amount, currency, provider, reference, status, metadata) VALUES(?,?,?,?,?,?,?,?)',
            [$u['id'], $planId, $price, 'KES', 'mpesa', $ref, 'pending', json_encode(['phone' => $phone, 'checkout' => $checkout, 'merchant' => $merchant])]
        );
    }
    $db->exec('UPDATE users SET phone=? WHERE id=?', [$phone, $u['id']]);
    flash('success', $result['message'] ?? 'Check your phone and enter your M-Pesa PIN to complete payment.');
    redirect('premium');
}

/**
 * Daraja STK callback — secured by:
 * - POST only, JSON body
 * - Optional IP allowlist (payment_settings metadata / config flag)
 * - Must match an existing pending transaction by CheckoutRequestID
 * - Idempotent activation (only pending → paid once)
 * - Never activates without ResultCode 0 + matching checkout id
 */
if ($path === 'premium/mpesa/callback' && $method === 'POST') {
    $raw = file_get_contents('php://input') ?: '';
    $clientIp = $_SERVER['HTTP_X_FORWARDED_FOR'] ?? $_SERVER['REMOTE_ADDR'] ?? '';
    if (str_contains($clientIp, ',')) {
        $clientIp = trim(explode(',', $clientIp)[0]);
    }

    // Log every attempt (no secrets)
    try {
        $logDir = dirname(__DIR__) . '/storage/logs';
        if (!is_dir($logDir)) {
            @mkdir($logDir, 0755, true);
        }
        @file_put_contents(
            $logDir . '/mpesa_callback.log',
            date('c') . " ip={$clientIp} bytes=" . strlen($raw) . "\n",
            FILE_APPEND
        );
    } catch (Throwable $e) {
        // ignore log failures
    }

    $settings = payment_settings();
    $strictIp = !empty($settings['mpesa_strict_ip'] ?? false);
    // Allow strict IP only when enabled in settings (column optional via metadata)
    if ($strictIp && !MpesaService::isLikelySafaricomIp($clientIp)) {
        http_response_code(403);
        header('Content-Type: application/json');
        echo json_encode(['ResultCode' => 1, 'ResultDesc' => 'Forbidden']);
        exit;
    }

    $body = json_decode($raw, true);
    if (!is_array($body)) {
        http_response_code(400);
        header('Content-Type: application/json');
        echo json_encode(['ResultCode' => 1, 'ResultDesc' => 'Invalid JSON']);
        exit;
    }

    $parsed = MpesaService::parseCallback($body);
    $checkout = $parsed['checkout_request_id'] ?? '';
    if ($checkout === '') {
        http_response_code(400);
        header('Content-Type: application/json');
        echo json_encode(['ResultCode' => 1, 'ResultDesc' => 'Missing CheckoutRequestID']);
        exit;
    }

    $tx = null;
    try {
        $tx = $db->one('SELECT * FROM payment_transactions WHERE checkout_request_id=? LIMIT 1', [$checkout]);
    } catch (Throwable $e) {
        $tx = null;
    }
    if (!$tx) {
        // Do not invent payments for unknown checkout IDs
        header('Content-Type: application/json');
        echo json_encode(['ResultCode' => 0, 'ResultDesc' => 'Accepted']);
        exit;
    }

    // Already finalized — idempotent
    if (in_array($tx['status'] ?? '', ['paid', 'failed', 'refunded'], true)) {
        header('Content-Type: application/json');
        echo json_encode(['ResultCode' => 0, 'ResultDesc' => 'Already processed']);
        exit;
    }

    if ($parsed['success']) {
        $receipt = $parsed['mpesa_receipt'] ?? '';
        if ($receipt === '') {
            // Require real M-Pesa receipt number on success
            $db->exec(
                'UPDATE payment_transactions SET status="failed", metadata=? WHERE id=? AND status="pending"',
                [json_encode(['error' => 'Success without receipt', 'callback' => $parsed]), $tx['id']]
            );
        } else {
            // Amount sanity: allow 1 KES tolerance
            $expected = (float) ($tx['amount'] ?? 0);
            $got = $parsed['amount'] !== null ? (float) $parsed['amount'] : $expected;
            if ($expected > 0 && abs($got - $expected) > 1) {
                $db->exec(
                    'UPDATE payment_transactions SET status="failed", metadata=? WHERE id=? AND status="pending"',
                    [json_encode(['error' => 'Amount mismatch', 'expected' => $expected, 'got' => $got, 'callback' => $parsed]), $tx['id']]
                );
            } else {
                $updated = $db->exec(
                    'UPDATE payment_transactions SET status="paid", reference=?, metadata=? WHERE id=? AND status="pending"',
                    [$receipt, json_encode(['callback' => $parsed, 'ip' => $clientIp]), $tx['id']]
                );
                // Only activate if we transitioned pending → paid
                $check = $db->one('SELECT status FROM payment_transactions WHERE id=?', [$tx['id']]);
                if (($check['status'] ?? '') === 'paid') {
                    activate_paid_access((int) $tx['user_id'], 'mpesa', $receipt, (float) ($tx['amount'] ?? monthly_price_kes()));
                }
            }
        }
    } else {
        $db->exec(
            'UPDATE payment_transactions SET status="failed", metadata=? WHERE id=? AND status="pending"',
            [json_encode(['callback' => $parsed, 'ip' => $clientIp]), $tx['id']]
        );
    }

    header('Content-Type: application/json');
    echo json_encode(['ResultCode' => 0, 'ResultDesc' => 'Accepted']);
    exit;
}

/* Poll pending M-Pesa (optional UI refresh) */

if ($path === 'premium/mpesa/retry' && $method === 'POST') {
    require_login();
    $u = current_user();
    $txId = (int) ($_POST['tx_id'] ?? 0);
    $settings = payment_settings();
    $mpesa = new MpesaService($settings);
    if (!$mpesa->isConfigured() || empty($settings['mpesa_enabled'])) {
        flash('error', 'M-Pesa is not available. Contact support or use card / wait for admin activation.');
        redirect('premium');
    }
    $tx = $db->one('SELECT * FROM payment_transactions WHERE id=? AND user_id=? AND provider="mpesa" AND status="pending" LIMIT 1', [$txId, $u['id']]);
    if (!$tx) {
        // also allow retry of failed
        $tx = $db->one('SELECT * FROM payment_transactions WHERE id=? AND user_id=? AND provider="mpesa" AND status="failed" LIMIT 1', [$txId, $u['id']]);
    }
    if (!$tx) {
        flash('error', 'No pending/failed M-Pesa payment found to retry.');
        redirect('premium');
    }
    $phone = MpesaService::normalizePhone((string) ($tx['phone'] ?? $u['phone'] ?? ''));
    if (!$phone) {
        flash('error', 'Add a valid phone number before retrying.');
        redirect('premium');
    }
    $ref = 'VL' . strtoupper(bin2hex(random_bytes(4)));
    $price = (float) ($tx['amount'] ?? monthly_price_kes());
    $result = $mpesa->stkPush($phone, $price, $ref, 'Velora Monthly');
    if (!$result['ok']) {
        flash('error', 'Retry failed: ' . ($result['message'] ?? 'Unknown error'));
        redirect('premium');
    }
    $checkout = $result['checkout_request_id'] ?? '';
    $merchant = $result['merchant_request_id'] ?? '';
    try {
        $db->exec(
            'UPDATE payment_transactions SET status="pending", reference=?, phone=?, checkout_request_id=?, merchant_request_id=?, metadata=? WHERE id=?',
            [$ref, $phone, $checkout, $merchant, json_encode(['retry' => true, 'stk' => $result['raw'] ?? [], 'attempts' => $result['attempts'] ?? 1]), $txId]
        );
    } catch (Throwable $e) {
        $db->exec('UPDATE payment_transactions SET status="pending", reference=? WHERE id=?', [$ref, $txId]);
    }
    flash('success', 'Payment retry sent — check your phone for the M-Pesa prompt.');
    redirect('premium');
}

if ($path === 'premium/mpesa/status' && $method === 'GET') {
    require_login();
    $u = current_user();
    $ref = trim($_GET['reference'] ?? '');
    $row = $db->one(
        'SELECT id, status, reference, checkout_request_id, created_at FROM payment_transactions WHERE user_id=? AND provider="mpesa" ORDER BY id DESC LIMIT 1',
        [$u['id']]
    );
    json_out(['ok' => true, 'transaction' => $row]);
}

/* ---------- Stripe Checkout ---------- */
if ($path === 'premium/card' && $method === 'POST') {
    require_login();
    $u = current_user();
    $settings = payment_settings();
    if (empty($settings['card_enabled'])) {
        flash('error', 'Card payments are disabled. Enable them in Admin → Payment integrations.');
        redirect('premium');
    }
    $stripe = new StripeService($settings);
    if (!$stripe->isConfigured()) {
        flash('error', 'Stripe secret key (sk_live_... or sk_test_...) is missing in Admin.');
        redirect('premium');
    }
    $price = monthly_price_kes();
    $plan = $db->one('SELECT id FROM subscription_plans WHERE name="Monthly Access" AND active=1 LIMIT 1');
    $planId = $plan ? (int) $plan['id'] : null;
    $ref = 'ST' . strtoupper(bin2hex(random_bytes(5)));

    $successUrl = url('premium/stripe/success');
    $cancelUrl = url('premium');
    $session = $stripe->createCheckoutSession(
        (int) $u['id'],
        (string) ($u['email'] ?? ''),
        (string) ($u['name'] ?? 'Member'),
        $price,
        $ref,
        $successUrl,
        $cancelUrl,
        $planId
    );
    if (!$session['ok']) {
        flash('error', $session['message'] ?? 'Could not start Stripe Checkout.');
        redirect('premium');
    }

    try {
        $db->exec(
            'INSERT INTO payment_transactions(user_id, plan_id, amount, currency, provider, reference, status, metadata) VALUES(?,?,?,?,?,?,?,?)',
            [
                $u['id'],
                $planId,
                $price,
                'KES',
                'stripe',
                $ref,
                'pending',
                json_encode(['checkout_session_id' => $session['session_id'] ?? '']),
            ]
        );
    } catch (Throwable $e) {
        flash('error', 'Could not record payment intent. Check database migration.');
        redirect('premium');
    }

    header('Location: ' . $session['url']);
    exit;
}

/* Stripe success return (secondary confirmation; webhook is source of truth) */
if ($path === 'premium/stripe/success' && $method === 'GET') {
    require_login();
    $u = current_user();
    $sessionId = trim($_GET['session_id'] ?? '');
    if ($sessionId === '') {
        flash('error', 'Missing Stripe session.');
        redirect('premium');
    }
    $settings = payment_settings();
    $stripe = new StripeService($settings);
    if (!$stripe->isConfigured()) {
        flash('error', 'Stripe is not configured.');
        redirect('premium');
    }
    $ret = $stripe->retrieveSession($sessionId);
    if (!$ret['ok']) {
        flash('error', $ret['message'] ?? 'Could not verify Stripe session.');
        redirect('premium');
    }
    $data = $ret['data'];
    $paid = ($data['payment_status'] ?? '') === 'paid';
    $ref = $data['client_reference_id'] ?? ($data['metadata']['reference'] ?? '');
    $metaUser = (int) ($data['metadata']['user_id'] ?? 0);
    if ($metaUser && $metaUser !== (int) $u['id']) {
        flash('error', 'Session does not belong to this account.');
        redirect('premium');
    }
    if ($paid && $ref !== '') {
        $tx = $db->one('SELECT * FROM payment_transactions WHERE reference=? AND user_id=? LIMIT 1', [$ref, $u['id']]);
        if ($tx && ($tx['status'] ?? '') === 'pending') {
            $db->exec(
                'UPDATE payment_transactions SET status="paid", metadata=? WHERE id=? AND status="pending"',
                [json_encode(['stripe_session' => $data]), $tx['id']]
            );
            activate_paid_access((int) $u['id'], 'stripe', $ref, (float) ($tx['amount'] ?? monthly_price_kes()));
            $auth->refresh();
            flash('success', 'Payment confirmed. Your blue verified badge is active for 30 days.');
        } elseif ($tx && ($tx['status'] ?? '') === 'paid') {
            flash('success', 'Payment already confirmed. Thank you!');
        } else {
            flash('success', 'Stripe reports paid. If access is not active yet, wait a moment for the webhook.');
        }
    } else {
        flash('error', 'Payment not completed. You can try again from Premium.');
    }
    redirect('premium');
}

/* Stripe webhook — signature required */
if ($path === 'premium/stripe/webhook' && $method === 'POST') {
    $payload = file_get_contents('php://input') ?: '';
    $sig = $_SERVER['HTTP_STRIPE_SIGNATURE'] ?? '';
    $settings = payment_settings();
    $stripe = new StripeService($settings);
    $event = $stripe->constructEvent($payload, $sig);
    if ($event === null) {
        http_response_code(400);
        header('Content-Type: application/json');
        echo json_encode(['error' => 'Invalid signature — set card_webhook_secret (whsec_...) in Admin']);
        exit;
    }
    $type = $event['type'] ?? '';
    $obj = $event['data']['object'] ?? [];
    $eventId = $event['id'] ?? '';

    // Log webhook (no full card data)
    try {
        $logDir = dirname(__DIR__) . '/storage/logs';
        if (!is_dir($logDir)) { @mkdir($logDir, 0755, true); }
        @file_put_contents($logDir . '/stripe_webhook.log', date('c') . " type={$type} id={$eventId}\n", FILE_APPEND);
    } catch (Throwable $e) {}

    $markPaidByRef = function (string $ref, int $userId, string $type, array $obj) use ($db) {
        if ($ref === '') return;
        $tx = $db->one('SELECT * FROM payment_transactions WHERE reference=? LIMIT 1', [$ref]);
        if (!$tx) return;
        if (($tx['status'] ?? '') === 'paid') return; // idempotent
        if (($tx['status'] ?? '') !== 'pending') return;
        $uid = $userId ?: (int) $tx['user_id'];
        $db->exec(
            'UPDATE payment_transactions SET status="paid", metadata=? WHERE id=? AND status="pending"',
            [json_encode(['stripe_event' => $type, 'object_id' => $obj['id'] ?? null]), $tx['id']]
        );
        $check = $db->one('SELECT status FROM payment_transactions WHERE id=?', [$tx['id']]);
        if (($check['status'] ?? '') === 'paid') {
            activate_paid_access($uid, 'stripe', $ref, (float) ($tx['amount'] ?? monthly_price_kes()));
        }
    };

    if (in_array($type, ['checkout.session.completed', 'checkout.session.async_payment_succeeded'], true)) {
        $ref = (string) ($obj['client_reference_id'] ?? ($obj['metadata']['reference'] ?? ''));
        $userId = (int) ($obj['metadata']['user_id'] ?? 0);
        $paymentStatus = (string) ($obj['payment_status'] ?? '');
        // completed can be unpaid for some modes; only activate when paid
        if ($paymentStatus === 'paid' || $type === 'checkout.session.async_payment_succeeded') {
            $markPaidByRef($ref, $userId, $type, $obj);
        }
    } elseif ($type === 'payment_intent.succeeded') {
        $ref = (string) ($obj['metadata']['reference'] ?? '');
        $userId = (int) ($obj['metadata']['user_id'] ?? 0);
        $markPaidByRef($ref, $userId, $type, $obj);
    } elseif (in_array($type, ['checkout.session.expired', 'checkout.session.async_payment_failed', 'payment_intent.payment_failed'], true)) {
        $ref = (string) ($obj['client_reference_id'] ?? ($obj['metadata']['reference'] ?? ''));
        if ($ref !== '') {
            $db->exec(
                'UPDATE payment_transactions SET status="failed", metadata=? WHERE reference=? AND status="pending"',
                [json_encode(['stripe_event' => $type]), $ref]
            );
        }
    }
    header('Content-Type: application/json');
    echo json_encode(['received' => true]);
    exit;
}

if ($path === 'premium/subscribe' && $method === 'POST') {
    require_login();
    flash('info', 'Use M-Pesa or Card on this page to activate Monthly Access. Demo payments are disabled.');
    redirect('premium');
}

if ($path === 'premium/boost' && $method === 'POST') {
    require_login();
    $u = current_user();
    if (!has_plan($u['id'], 'Gold')) {
        flash('error', 'Boost requires Gold or VIP.');
        redirect('premium');
    }
    $db->exec(
        'INSERT INTO boosts(user_id, starts_at, ends_at, status, source) VALUES(?, NOW(), DATE_ADD(NOW(), INTERVAL 30 MINUTE), "active", "subscription")',
        [$u['id']]
    );
    flash('success', 'Your 30-minute Boost is active.');
    redirect('premium');
}

/* ---------- Safety / Settings ---------- */
if ($path === 'safety') {
    require_login();
    view('safety/index.php');
}

if ($path === 'settings' && $method === 'GET') {
    require_login();
    $u = current_user();
    ensure_user_rows((int) $u['id']);
    $p = $db->one('SELECT * FROM user_profiles WHERE user_id=?', [$u['id']]) ?: [];
    $prefs = $db->one('SELECT * FROM notification_preferences WHERE user_id=?', [$u['id']]) ?: [];
    view('settings/index.php', compact('u', 'p', 'prefs'));
}

if ($path === 'settings/save' && $method === 'POST') {
    require_login();
    $u = current_user();
    ensure_user_rows((int) $u['id']);
    $db->exec(
        'UPDATE user_profiles SET visibility=?, show_distance=?, incognito=? WHERE user_id=?',
        [isset($_POST['visibility']) ? 1 : 0, isset($_POST['show_distance']) ? 1 : 0, isset($_POST['incognito']) ? 1 : 0, $u['id']]
    );
    $db->exec(
        'UPDATE notification_preferences SET email_enabled=?, push_enabled=?, match_enabled=?, message_enabled=?, marketing_enabled=? WHERE user_id=?',
        [
            isset($_POST['email_enabled']) ? 1 : 0,
            isset($_POST['push_enabled']) ? 1 : 0,
            isset($_POST['match_enabled']) ? 1 : 0,
            isset($_POST['message_enabled']) ? 1 : 0,
            isset($_POST['marketing_enabled']) ? 1 : 0,
            $u['id'],
        ]
    );
    flash('success', 'Settings saved.');
    redirect('settings');
}

/* ---------- Admin ---------- */
if ($path === 'admin') {
    require_role('admin');
    $stats = [
        'users' => $db->one('SELECT COUNT(*) c FROM users WHERE deleted_at IS NULL')['c'] ?? 0,
        'active' => $db->one('SELECT COUNT(*) c FROM users WHERE status="active" AND deleted_at IS NULL')['c'] ?? 0,
        'matches' => $db->one('SELECT COUNT(*) c FROM matches')['c'] ?? 0,
        'messages' => $db->one('SELECT COUNT(*) c FROM messages')['c'] ?? 0,
        'subscriptions' => $db->one('SELECT COUNT(*) c FROM user_subscriptions WHERE status="active"')['c'] ?? 0,
        'reports' => $db->one('SELECT COUNT(*) c FROM reports WHERE status="open"')['c'] ?? 0,
        'revenue' => $db->one('SELECT COALESCE(SUM(amount),0) s FROM payment_transactions WHERE status="paid"')['s'] ?? 0,
        'paid_users' => 0,
        'visits_today' => 0,
        'visits_7d' => 0,
        'unique_sessions_today' => 0,
        'unique_sessions_7d' => 0,
        'visitors_with_geo' => 0,
        'countries' => 0,
        'cities' => 0,
    ];
    try {
        $stats['paid_users'] = $db->one('SELECT COUNT(*) c FROM users WHERE is_verified_paid=1 AND (paid_until IS NULL OR paid_until > NOW()) AND deleted_at IS NULL')['c'] ?? 0;
    } catch (Throwable $e) {}
    try {
        $stats['visits_today'] = $db->one('SELECT COUNT(*) c FROM visitor_events WHERE created_at >= CURDATE() AND is_bot=0')['c'] ?? 0;
        $stats['visits_7d'] = $db->one('SELECT COUNT(*) c FROM visitor_events WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND is_bot=0')['c'] ?? 0;
        $stats['unique_sessions_today'] = $db->one('SELECT COUNT(DISTINCT session_key) c FROM visitor_events WHERE created_at >= CURDATE() AND is_bot=0')['c'] ?? 0;
        $stats['unique_sessions_7d'] = $db->one('SELECT COUNT(DISTINCT session_key) c FROM visitor_events WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND is_bot=0')['c'] ?? 0;
        $stats['visitors_with_geo'] = $db->one('SELECT COUNT(*) c FROM visitor_events WHERE latitude IS NOT NULL AND created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)')['c'] ?? 0;
        $stats['countries'] = $db->one('SELECT COUNT(DISTINCT country) c FROM visitor_events WHERE country IS NOT NULL AND country<>"" AND created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)')['c'] ?? 0;
        $stats['cities'] = $db->one('SELECT COUNT(DISTINCT city) c FROM visitor_events WHERE city IS NOT NULL AND city<>"" AND created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)')['c'] ?? 0;
    } catch (Throwable $e) {}
    $visitorCountries = [];
    $visitorCities = [];
    $recentVisits = [];
    try {
        $visitorCountries = $db->all(
            'SELECT country, COUNT(*) c FROM visitor_events
             WHERE country IS NOT NULL AND country<>"" AND is_bot=0 AND created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
             GROUP BY country ORDER BY c DESC LIMIT 12'
        );
        $visitorCities = $db->all(
            'SELECT city, country, COUNT(*) c FROM visitor_events
             WHERE city IS NOT NULL AND city<>"" AND is_bot=0 AND created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
             GROUP BY city, country ORDER BY c DESC LIMIT 12'
        );
        $recentVisits = $db->all(
            'SELECT v.*, u.name AS user_name, u.email AS user_email
             FROM visitor_events v
             LEFT JOIN users u ON u.id = v.user_id
             WHERE v.is_bot=0
             ORDER BY v.id DESC LIMIT 40'
        );
    } catch (Throwable $e) {}
    try {
        $users = $db->all(
            'SELECT u.id, u.name, u.email, u.status, u.role, u.created_at, u.last_seen_at, u.paid_until, u.is_verified_paid,
                    p.city, p.profile_completion,
                    (SELECT balance FROM credit_wallets cw WHERE cw.user_id=u.id) AS credits
             FROM users u
             LEFT JOIN user_profiles p ON p.user_id = u.id
             WHERE u.deleted_at IS NULL
             ORDER BY u.created_at DESC LIMIT 100'
        );
    } catch (Throwable $e) {
        $users = $db->all(
            'SELECT u.id, u.name, u.email, u.status, u.role, u.created_at, u.last_seen_at, p.city, p.profile_completion,
                    (SELECT balance FROM credit_wallets cw WHERE cw.user_id=u.id) AS credits
             FROM users u
             LEFT JOIN user_profiles p ON p.user_id = u.id
             WHERE u.deleted_at IS NULL
             ORDER BY u.created_at DESC LIMIT 100'
        );
    }
    $reports = $db->all(
        'SELECT r.*, a.name AS reporter, b.name AS reported
         FROM reports r
         JOIN users a ON a.id = r.reporter_id
         JOIN users b ON b.id = r.reported_user_id
         ORDER BY FIELD(r.status,"open","reviewing","resolved","dismissed"), r.created_at DESC
         LIMIT 50'
    );
    $plans = $db->all('SELECT * FROM subscription_plans ORDER BY sort_order');
    $payments = $db->all(
        'SELECT pt.*, u.name, sp.name AS plan_name
         FROM payment_transactions pt
         JOIN users u ON u.id = pt.user_id
         LEFT JOIN subscription_plans sp ON sp.id = pt.plan_id
         ORDER BY pt.created_at DESC LIMIT 50'
    );
    $logs = $db->all(
        'SELECT l.*, u.name AS admin_name FROM admin_activity_logs l
         LEFT JOIN users u ON u.id = l.admin_id
         ORDER BY l.created_at DESC LIMIT 30'
    );
    $verifications = $db->all(
        'SELECT v.*, u.name, u.email FROM verification_requests v
         JOIN users u ON u.id = v.user_id
         ORDER BY FIELD(v.status,"pending","approved","rejected","expired"), v.created_at DESC LIMIT 20'
    );
        $paySettings = payment_settings();
    view('admin/dashboard.php', compact('stats', 'users', 'reports', 'plans', 'payments', 'logs', 'verifications', 'paySettings', 'visitorCountries', 'visitorCities', 'recentVisits'));
}

if (str_starts_with($path, 'admin/') && $method === 'POST') {
    require_role('admin');
    if ($path === 'admin/user/status') {
        $id = (int) ($_POST['user_id'] ?? 0);
        $status = $_POST['status'] ?? '';
        if (in_array($status, ['active', 'paused', 'suspended', 'banned'], true)) {
            $db->exec('UPDATE users SET status=? WHERE id=? AND role<>"admin"', [$status, $id]);
            log_admin('Changed user status', 'user', $id, ['status' => $status]);
        }
        redirect('admin');
    }
    if ($path === 'admin/user/role') {
        $id = (int) ($_POST['user_id'] ?? 0);
        $role = $_POST['role'] ?? '';
        if (in_array($role, ['user', 'moderator', 'support', 'finance', 'verification'], true)) {
            $db->exec('UPDATE users SET role=? WHERE id=? AND role<>"admin"', [$role, $id]);
            log_admin('Changed user role', 'user', $id, ['role' => $role]);
        }
        redirect('admin');
    }
    if ($path === 'admin/report/status') {
        $id = (int) ($_POST['report_id'] ?? 0);
        $status = $_POST['status'] ?? '';
        if (in_array($status, ['open', 'reviewing', 'resolved', 'dismissed'], true)) {
            $db->exec(
                'UPDATE reports SET status=?, resolved_at=IF(? IN ("resolved","dismissed"), NOW(), NULL) WHERE id=?',
                [$status, $status, $id]
            );
            log_admin('Updated report', 'report', $id, ['status' => $status]);
        }
        redirect('admin');
    }
    
    if ($path === 'admin/payment-settings') {
        $fields = [
            'mpesa_enabled' => isset($_POST['mpesa_enabled']) ? 1 : 0,
            'mpesa_env' => in_array($_POST['mpesa_env'] ?? '', ['sandbox', 'live'], true) ? $_POST['mpesa_env'] : 'sandbox',
            'mpesa_consumer_key' => trim($_POST['mpesa_consumer_key'] ?? ''),
            'mpesa_consumer_secret' => trim($_POST['mpesa_consumer_secret'] ?? ''),
            'mpesa_shortcode' => trim($_POST['mpesa_shortcode'] ?? ''),
            'mpesa_passkey' => trim($_POST['mpesa_passkey'] ?? ''),
            'mpesa_callback_url' => trim($_POST['mpesa_callback_url'] ?? ''),
            'card_enabled' => isset($_POST['card_enabled']) ? 1 : 0,
            'card_provider' => trim($_POST['card_provider'] ?? 'stripe'),
            'card_publishable_key' => trim($_POST['card_publishable_key'] ?? ''),
            'card_secret_key' => trim($_POST['card_secret_key'] ?? ''),
            'card_webhook_secret' => trim($_POST['card_webhook_secret'] ?? ''),
            'monthly_price_kes' => max(1, (float) ($_POST['monthly_price_kes'] ?? 500)),
            'trial_hours' => max(1, (int) ($_POST['trial_hours'] ?? 72)),
            'mpesa_strict_ip' => isset($_POST['mpesa_strict_ip']) ? 1 : 0,
        ];
        try {
            $exists = $db->one('SELECT id FROM payment_settings WHERE id=1');
            if ($exists) {
                $db->exec(
                    'UPDATE payment_settings SET
                     mpesa_enabled=?, mpesa_env=?, mpesa_consumer_key=?, mpesa_consumer_secret=?,
                     mpesa_shortcode=?, mpesa_passkey=?, mpesa_callback_url=?,
                     card_enabled=?, card_provider=?, card_publishable_key=?, card_secret_key=?, card_webhook_secret=?,
                     monthly_price_kes=?, trial_hours=?, mpesa_strict_ip=?, updated_at=NOW(), updated_by=?
                     WHERE id=1',
                    [
                        $fields['mpesa_enabled'], $fields['mpesa_env'], $fields['mpesa_consumer_key'], $fields['mpesa_consumer_secret'],
                        $fields['mpesa_shortcode'], $fields['mpesa_passkey'], $fields['mpesa_callback_url'],
                        $fields['card_enabled'], $fields['card_provider'], $fields['card_publishable_key'], $fields['card_secret_key'], $fields['card_webhook_secret'],
                        $fields['monthly_price_kes'], $fields['trial_hours'], $fields['mpesa_strict_ip'], current_user()['id'],
                    ]
                );
            } else {
                $db->exec(
                    'INSERT INTO payment_settings(id, mpesa_enabled, mpesa_env, mpesa_consumer_key, mpesa_consumer_secret, mpesa_shortcode, mpesa_passkey, mpesa_callback_url, card_enabled, card_provider, card_publishable_key, card_secret_key, card_webhook_secret, monthly_price_kes, trial_hours, mpesa_strict_ip, updated_at, updated_by)
                     VALUES(1,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,NOW(),?)',
                    [
                        $fields['mpesa_enabled'], $fields['mpesa_env'], $fields['mpesa_consumer_key'], $fields['mpesa_consumer_secret'],
                        $fields['mpesa_shortcode'], $fields['mpesa_passkey'], $fields['mpesa_callback_url'],
                        $fields['card_enabled'], $fields['card_provider'], $fields['card_publishable_key'], $fields['card_secret_key'], $fields['card_webhook_secret'],
                        $fields['monthly_price_kes'], $fields['trial_hours'], $fields['mpesa_strict_ip'], current_user()['id'],
                    ]
                );
            }
            // Keep Monthly Access plan price in sync
            $db->exec('UPDATE subscription_plans SET price_kes=? WHERE name="Monthly Access"', [$fields['monthly_price_kes']]);
            log_admin('Updated payment settings', 'payment_settings', 1, ['mpesa' => (bool)$fields['mpesa_enabled'], 'card' => (bool)$fields['card_enabled']]);
            flash('success', 'Payment settings saved.');
        } catch (Throwable $e) {
            flash('error', 'Could not save payment settings. Import database/migrate_mandatory_subscription.sql first. ' . $e->getMessage());
        }
        redirect('admin');
    }


    if ($path === 'admin/grant-premium') {
        $id = (int) ($_POST['user_id'] ?? 0);
        $days = max(1, min(365, (int) ($_POST['days'] ?? 30)));
        $note = trim($_POST['note'] ?? 'Manual admin activation');
        $user = $id ? $db->one('SELECT id, name, email, role FROM users WHERE id=? AND deleted_at IS NULL', [$id]) : null;
        if (!$user) {
            flash('error', 'Select a valid user.');
            redirect('admin');
        }
        $ref = 'ADMIN-MANUAL-' . strtoupper(bin2hex(random_bytes(4)));
        $price = monthly_price_kes();
        $plan = $db->one('SELECT id FROM subscription_plans WHERE name="Monthly Access" AND active=1 LIMIT 1')
            ?: $db->one('SELECT id FROM subscription_plans WHERE price_kes > 0 AND active=1 ORDER BY price_kes ASC LIMIT 1');
        $planId = $plan ? (int) $plan['id'] : null;

        try {
            $db->exec(
                'INSERT INTO payment_transactions(user_id, plan_id, amount, currency, provider, reference, status, metadata) VALUES(?,?,?,?,?,?,?,?)',
                [
                    $id,
                    $planId,
                    $price,
                    'KES',
                    'admin_manual',
                    $ref,
                    'paid',
                    json_encode(['note' => $note, 'days' => $days, 'admin_id' => current_user()['id']]),
                ]
            );
        } catch (Throwable $e) {
            // continue even if payments table shape differs
        }

        // Activate for N days (activate_paid_access is 30-day fixed — extend manually)
        $db->exec('UPDATE user_subscriptions SET status="expired" WHERE user_id=? AND status="active"', [$id]);
        if ($planId) {
            $db->exec(
                'INSERT INTO user_subscriptions(user_id, plan_id, status, starts_at, ends_at, payment_provider, provider_reference) VALUES(?,?,"active",NOW(),DATE_ADD(NOW(), INTERVAL ? DAY),?,?)',
                [$id, $planId, $days, 'admin_manual', $ref]
            );
        }
        try {
            $db->exec(
                'UPDATE users SET paid_until=DATE_ADD(NOW(), INTERVAL ? DAY), is_verified_paid=1, updated_at=NOW() WHERE id=?',
                [$days, $id]
            );
        } catch (Throwable $e) {
            // columns may be missing on very old DBs
        }
        notify($id, 'system', 'Premium activated', 'An administrator activated your Monthly Access for ' . $days . ' days. Inbox and messaging are unlocked.');
        log_admin('Manual premium activation', 'user', $id, ['days' => $days, 'reference' => $ref, 'note' => $note]);
        flash('success', 'Premium activated for ' . ($user['name'] ?? 'user') . ' (' . ($user['email'] ?? '') . ') — ' . $days . ' days. Blue badge + inbox unlocked.');
        redirect('admin');
    }

    if ($path === 'admin/revoke-premium') {
        $id = (int) ($_POST['user_id'] ?? 0);
        if ($id) {
            $db->exec('UPDATE user_subscriptions SET status="expired", ends_at=NOW() WHERE user_id=? AND status="active"', [$id]);
            try {
                $db->exec('UPDATE users SET paid_until=NOW(), is_verified_paid=0, updated_at=NOW() WHERE id=? AND role<>"admin"', [$id]);
            } catch (Throwable $e) {}
            log_admin('Revoked premium', 'user', $id, []);
            flash('success', 'Premium access revoked for user #' . $id . '.');
        }
        redirect('admin');
    }

if ($path === 'admin/plan/save') {
        $id = (int) ($_POST['plan_id'] ?? 0);
        $price = max(0, (float) ($_POST['price_kes'] ?? 0));
        $active = isset($_POST['active']) ? 1 : 0;
        $db->exec(
            'UPDATE subscription_plans SET price_kes=?, description=?, active=? WHERE id=?',
            [$price, trim($_POST['description'] ?? ''), $active, $id]
        );
        log_admin('Updated subscription plan', 'plan', $id);
        redirect('admin');
    }
    if ($path === 'admin/credit') {
        $id = (int) ($_POST['user_id'] ?? 0);
        $amount = (int) ($_POST['amount'] ?? 0);
        if ($amount && $id) {
            $db->exec(
                'INSERT INTO credit_wallets(user_id, balance) VALUES(?,?) ON DUPLICATE KEY UPDATE balance=balance+VALUES(balance)',
                [$id, $amount]
            );
            $db->exec(
                'INSERT INTO credit_transactions(user_id, amount, reason, reference) VALUES(?,?,?,?)',
                [$id, $amount, 'Admin adjustment', 'ADMIN-' . time()]
            );
            log_admin('Adjusted credits', 'user', $id, ['amount' => $amount]);
        }
        redirect('admin');
    }
    if ($path === 'admin/verification') {
        $id = (int) ($_POST['request_id'] ?? 0);
        $status = $_POST['status'] ?? '';
        if ($id && in_array($status, ['approved', 'rejected', 'expired'], true)) {
            $db->exec(
                'UPDATE verification_requests SET status=?, reviewer_id=?, reviewed_at=NOW() WHERE id=?',
                [$status, current_user()['id'], $id]
            );
            log_admin('Reviewed verification', 'verification', $id, ['status' => $status]);
        }
        redirect('admin');
    }
}

http_response_code(404);
view('layouts/404.php');
