<?php
require_once APP_PATH . '/Core/BaseController.php';
require_once APP_PATH . '/Models/User.php';
require_once APP_PATH . '/Models/University.php';
require_once APP_PATH . '/Models/Scholarship.php';
require_once APP_PATH . '/Models/Mentor.php';
require_once APP_PATH . '/Core/CSRF.php';

class AdminController extends BaseController {

    private User $userModel;
    private University $uniModel;
    private Scholarship $schModel;
    private Mentor $mentorModel;

    public function __construct() {
        $this->userModel   = new User();
        $this->uniModel    = new University();
        $this->schModel    = new Scholarship();
        $this->mentorModel = new Mentor();
    }

    // ── GET /admin ───────────────────────────────────────────────
    public function dashboard(): void {
        $db = Database::getInstance();
        $roleCounts = $this->userModel->countByRole();
        
        $stats = [
            'total_students'      => $roleCounts['student'] ?? 0,
            'total_mentors'       => $roleCounts['mentor']  ?? 0,
            'total_tutors'        => $roleCounts['tutor']   ?? 0,
            'total_admins'        => $roleCounts['admin']   ?? 0,
            'sessions_completed'  => (int)$db->query("SELECT COUNT(*) FROM sessions WHERE status='completed'")->fetchColumn(),
            'total_scholarships'  => (int)$db->query("SELECT COUNT(*) FROM scholarships")->fetchColumn(),
            'total_universities'  => (int)$db->query("SELECT COUNT(*) FROM universities")->fetchColumn(),
            'total_messages'      => (int)$db->query("SELECT COUNT(*) FROM messages")->fetchColumn(),
            'messages_today'      => (int)$db->query("SELECT COUNT(*) FROM messages WHERE DATE(created_at)=CURDATE()")->fetchColumn(),
            'new_students_week'   => (int)$db->query("SELECT COUNT(*) FROM users WHERE role='student' AND created_at >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)")->fetchColumn(),
            'new_mentors_week'    => (int)$db->query("SELECT COUNT(*) FROM users WHERE role='mentor' AND created_at >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)")->fetchColumn(),
            'sessions_this_month' => (int)$db->query("SELECT COUNT(*) FROM sessions WHERE status='completed' AND MONTH(created_at) = MONTH(CURDATE()) AND YEAR(created_at) = YEAR(CURDATE())")->fetchColumn(),
            'new_scholarships'    => (int)$db->query("SELECT COUNT(*) FROM scholarships WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)")->fetchColumn(),
        ];

        // 30-day registration trend (students vs mentors)
        $trendData = $db->query("SELECT DATE(created_at) as date, role, COUNT(*) as count FROM users WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND role IN ('student', 'mentor') GROUP BY DATE(created_at), role")->fetchAll();
        $registrationTrendRaw = [];
        for ($i = 29; $i >= 0; $i--) {
            $date = date('Y-m-d', strtotime("-$i days"));
            $registrationTrendRaw[$date] = ['date' => date('M d', strtotime($date)), 'students' => 0, 'mentors' => 0];
        }
        foreach ($trendData as $row) {
            $date = $row['date'];
            if (isset($registrationTrendRaw[$date])) {
                if ($row['role'] === 'student') $registrationTrendRaw[$date]['students'] = $row['count'];
                if ($row['role'] === 'mentor')  $registrationTrendRaw[$date]['mentors'] = $row['count'];
            }
        }
        $registrationTrend = array_values($registrationTrendRaw);

        // Recent users mapped for view
        $recentUsersRaw = $this->userModel->recentUsers(8);
        $recentUsers = array_map(function($u) {
            $u['full_name'] = $u['first_name'] . ' ' . $u['last_name'];
            $u['profile_picture'] = avatarUrl($u['avatar'], $u['full_name']);
            $u['is_active'] = ($u['status'] === 'active');
            return $u;
        }, $recentUsersRaw);

        // Pending mentors mapped for view
        $pendingMentorsRaw = $db->query("SELECT * FROM users WHERE role='mentor' AND status='pending' ORDER BY created_at DESC")->fetchAll();
        $pendingMentors = array_map(function($m) {
            $m['full_name'] = $m['first_name'] . ' ' . $m['last_name'];
            $m['profile_picture'] = avatarUrl($m['avatar'], $m['full_name']);
            $m['expertise'] = 'Mentor';
            return $m;
        }, $pendingMentorsRaw);

        $this->view('admin.dashboard', [
            'title'             => 'Admin Dashboard — BOAFO',
            'stats'             => $stats,
            'recentUsers'       => $recentUsers,
            'registrationTrend' => $registrationTrend,
            'pendingMentors'    => $pendingMentors,
        ]);
    }

    // ── GET /admin/users ─────────────────────────────────────────
    public function users(): void {
        $page   = max(1,(int)($_GET['page']??1));
        $q      = trim($_GET['q']??'');
        $role   = $_GET['role']??'';
        $result = $this->userModel->searchUsers($q, $role ?: null, $page);
        $this->view('admin.users', [
            'title'      => 'Manage Users — Admin — BOAFO',
            'users'      => $result['items'],
            'pagination' => $result,
            'q'          => $q,
            'role_filter'=> $role,
        ]);
    }

    // ── GET /admin/users/{id} ─────────────────────────────────────
    public function viewUser(string $id): void {
        $db = Database::getInstance();

        // Fetch user + profile data via a single join
        $stmt = $db->prepare(
            "SELECT u.*,
                    sp.gender AS gender,
                    sp.shs_school AS school,
                    sp.shs_program AS shs_programme,
                    sp.intended_program AS target_programme,
                    sp.preferred_university AS target_university,
                    sp.bio AS bio,
                    sp.region_id AS region,
                    mp.expertise_areas AS expertise,
                    mp.program AS profession,
                    mp.verified AS is_pending_approval,
                    uni.name AS university
             FROM users u
             LEFT JOIN student_profiles sp ON sp.user_id = u.id
             LEFT JOIN mentor_profiles mp ON mp.user_id = u.id
             LEFT JOIN universities uni ON uni.id = mp.university_id
             WHERE u.id = ? LIMIT 1"
        );
        $stmt->execute([(int)$id]);
        $user = $stmt->fetch();

        if (!$user) { http_response_code(404); return; }

        $user['full_name'] = trim($user['first_name'] . ' ' . $user['last_name']);
        // Normalize: pending approval = mentor not yet verified
        $user['is_pending_approval'] = ($user['role'] === 'mentor' && !$user['is_pending_approval']);

        $activityStats  = [];
        $recentActivity = [];

        if ($user['role'] === 'student') {
            $s = $db->prepare(
                "SELECT
                    (SELECT COUNT(*) FROM sessions WHERE student_id = :uid) AS session_count,
                    (SELECT COUNT(*) FROM messages WHERE sender_id = :uid2) AS message_count,
                    (SELECT COUNT(*) FROM transition_checklists WHERE user_id = :uid3 AND is_completed = 1) AS checklist_completed"
            );
            $s->execute([':uid' => (int)$id, ':uid2' => (int)$id, ':uid3' => (int)$id]);
            $activityStats = $s->fetch() ?: [];
        } elseif ($user['role'] === 'mentor') {
            $s = $db->prepare(
                "SELECT
                    (SELECT COUNT(DISTINCT student_id) FROM mentorship_requests WHERE mentor_id = :uid) AS students_count,
                    (SELECT COUNT(*) FROM sessions WHERE mentor_id = :uid2) AS session_count,
                    (SELECT AVG(rating) FROM sessions WHERE mentor_id = :uid3 AND rating IS NOT NULL) AS avg_rating"
            );
            $s->execute([':uid' => (int)$id, ':uid2' => (int)$id, ':uid3' => (int)$id]);
            $activityStats = $s->fetch() ?: [];
        }

        // Recent audit log entries for this user as recent activity
        $logStmt = $db->prepare(
            "SELECT action AS description, created_at FROM audit_logs WHERE user_id = ? ORDER BY created_at DESC LIMIT 10"
        );
        $logStmt->execute([(int)$id]);
        $recentActivity = $logStmt->fetchAll();

        $this->view('admin.user_detail', [
            'title'          => $user['full_name'] . ' — Admin — BOAFO',
            'user'           => $user,
            'activityStats'  => $activityStats,
            'recentActivity' => $recentActivity,
        ]);
    }

    // ── POST /admin/users/{id}/status ─────────────────────────────
    public function updateUserStatus(string $id): void {
        CSRF::check();
        $status = $this->input('status', 'active');
        $allowed = ['active','inactive','suspended'];
        if (!in_array($status, $allowed)) {
            $this->json(['success'=>false,'message'=>'Invalid status.'], 400);
        }
        $this->userModel->update((int)$id, ['status'=>$status]);
        $this->logAudit('update_user_status', 'users', (int)$id, ['status'=>$status]);
        if ($this->isAjax()) $this->json(['success'=>true]);
        $this->flash('success','User status updated.');
        $this->redirect(APP_URL . '/admin/users');
    }

    // ── GET /admin/universities ───────────────────────────────────
    public function universities(): void {
        $page   = max(1, (int)($_GET['page'] ?? 1));
        $search = trim($_GET['search'] ?? '');
        $type   = $_GET['type'] ?? '';
        $status = $_GET['status'] ?? '';

        $db = Database::getInstance();
        $where = ['1=1'];
        $params = [];

        if ($search !== '') {
            $where[] = "(name LIKE ? OR city LIKE ?)";
            $params[] = "%{$search}%";
            $params[] = "%{$search}%";
        }
        if ($type !== '') {
            $where[] = "university_type = ?";
            $params[] = $type;
        }
        if ($status !== '') {
            $where[] = "status = ?";
            $params[] = $status === '1' ? 'active' : 'inactive';
        }

        $whereClause = implode(' AND ', $where);
        
        $totalStmt = $db->prepare("SELECT COUNT(*) FROM universities WHERE {$whereClause}");
        $totalStmt->execute($params);
        $total = (int) $totalStmt->fetchColumn();

        $totalPages = ceil($total / ITEMS_PER_PAGE) ?: 1;
        $offset = ($page - 1) * ITEMS_PER_PAGE;

        $stmt = $db->prepare("SELECT * FROM universities WHERE {$whereClause} ORDER BY name ASC LIMIT ? OFFSET ?");
        $stmt->execute([...$params, ITEMS_PER_PAGE, $offset]);
        $universities = $stmt->fetchAll();

        // Get program and scholarship counts for each university
        foreach ($universities as &$uni) {
            $progStmt = $db->prepare("SELECT COUNT(*) FROM programs WHERE university_id = ?");
            $progStmt->execute([$uni['id']]);
            $uni['program_count'] = $progStmt->fetchColumn();

            $schStmt = $db->prepare("SELECT COUNT(*) FROM scholarship_universities WHERE university_id = ?");
            $schStmt->execute([$uni['id']]);
            $uni['scholarship_count'] = $schStmt->fetchColumn();
        }

        $this->view('admin.universities', [
            'title'          => 'Manage Universities — Admin — BOAFO',
            'universities'   => $universities,
            'total'          => $total,
            'totalPages'     => $totalPages,
            'currentPage'    => $page,
            'search'         => $search,
            'selectedType'   => $type,
            'selectedStatus' => $status,
        ]);
    }

    // ── GET /admin/universities/create ───────────────────────────
    public function createUniversityForm(): void {
        $db = Database::getInstance();
        $countries = $db->query("SELECT * FROM countries ORDER BY name")->fetchAll();
        $regions   = $db->query("SELECT * FROM regions ORDER BY name")->fetchAll();
        $this->view('admin.university_form', [
            'title'     => 'Add University — Admin — BOAFO',
            'action'    => 'create',
            'university'=> [],
            'countries' => $countries,
            'regions'   => $regions,
            'errors'    => $this->getFlash('errors') ?? [],
        ]);
    }

    // ── POST /admin/universities/create ──────────────────────────
    public function createUniversity(): void {
        CSRF::check();
        $data = $this->collectUniversityData();
        // Handle logo upload
        if (!empty($_FILES['logo']['name'])) {
            $logo = uploadFile($_FILES['logo'], 'logos', ALLOWED_IMAGE_TYPES);
            if ($logo) $data['logo'] = $logo;
        }
        // Handle banner upload
        if (!empty($_FILES['banner']['name'])) {
            $banner = uploadFile($_FILES['banner'], 'banners', ALLOWED_IMAGE_TYPES);
            if ($banner) $data['banner'] = $banner;
        }
        $id = $this->uniModel->createWithSlug($data);
        $this->logAudit('create_university', 'universities', $id, $data);
        $this->flash('success', 'University created successfully!');
        $this->redirect(APP_URL . '/admin/universities');
    }

    // ── GET /admin/universities/{id}/edit ────────────────────────
    public function editUniversityForm(string $id): void {
        $university = $this->uniModel->find((int)$id);
        if (!$university) { http_response_code(404); return; }
        $db = Database::getInstance();
        $countries = $db->query("SELECT * FROM countries ORDER BY name")->fetchAll();
        $regions   = $db->query("SELECT * FROM regions ORDER BY name")->fetchAll();
        $this->view('admin.university_form', [
            'title'      => 'Edit University — Admin — BOAFO',
            'action'     => 'edit',
            'university' => $university,
            'countries'  => $countries,
            'regions'    => $regions,
            'errors'     => $this->getFlash('errors') ?? [],
        ]);
    }

    // ── POST /admin/universities/{id}/edit ───────────────────────
    public function editUniversity(string $id): void {
        CSRF::check();
        $data = $this->collectUniversityData();
        // Handle logo upload
        if (!empty($_FILES['logo']['name'])) {
            $logo = uploadFile($_FILES['logo'], 'logos', ALLOWED_IMAGE_TYPES);
            if ($logo) $data['logo'] = $logo;
        }
        // Handle banner upload
        if (!empty($_FILES['banner']['name'])) {
            $banner = uploadFile($_FILES['banner'], 'banners', ALLOWED_IMAGE_TYPES);
            if ($banner) $data['banner'] = $banner;
        }
        $this->uniModel->update((int)$id, $data);
        $this->logAudit('edit_university', 'universities', (int)$id, $data);
        $this->flash('success', 'University updated!');
        $this->redirect(APP_URL . '/admin/universities');
    }

    // ── POST /admin/universities/{id}/delete ─────────────────────
    public function deleteUniversity(string $id): void {
        CSRF::check();
        $this->uniModel->delete((int)$id);
        $this->logAudit('delete_university', 'universities', (int)$id);
        if ($this->isAjax()) $this->json(['success'=>true]);
        $this->flash('success','University deleted.');
        $this->redirect(APP_URL . '/admin/universities');
    }

    // ── GET /admin/universities/{id}/programmes ───────────────────
    public function universityProgrammes(string $id): void {
        $university = $this->uniModel->find((int)$id);
        if (!$university) { $this->abort(404); return; }
        $db = Database::getInstance();
        $programmes = $db->prepare(
            "SELECT p.*, f.name AS faculty_name
             FROM programs p
             LEFT JOIN faculties f ON f.id = p.faculty_id
             WHERE p.university_id = ?
             ORDER BY p.degree_level, p.name"
        );
        $programmes->execute([(int)$id]);
        $this->view('admin.university_programmes', [
            'title'       => 'Manage Programmes — ' . $university['name'],
            'university'  => $university,
            'programmes'  => $programmes->fetchAll(),
        ]);
    }

    // ── GET /admin/universities/{id}/programmes/create ────────────
    public function createProgrammeForm(string $id): void {
        $university = $this->uniModel->find((int)$id);
        if (!$university) { $this->abort(404); return; }
        $db = Database::getInstance();
        $faculties = $db->prepare("SELECT * FROM faculties WHERE university_id = ? ORDER BY name");
        $faculties->execute([(int)$id]);
        $this->view('admin.programme_form', [
            'title'      => 'Add Programme — ' . $university['name'],
            'university' => $university,
            'programme'  => [],
            'faculties'  => $faculties->fetchAll(),
            'errors'     => $this->getFlash('errors') ?? [],
        ]);
    }

    // ── POST /admin/universities/{id}/programmes/create ───────────
    public function createProgramme(string $id): void {
        CSRF::check();
        $university = $this->uniModel->find((int)$id);
        if (!$university) { $this->abort(404); return; }
        $db = Database::getInstance();
        $data = [
            'university_id'         => (int)$id,
            'faculty_id'            => !empty($_POST['faculty_id']) ? (int)$_POST['faculty_id'] : null,
            'name'                  => trim($_POST['name'] ?? ''),
            'degree_level'          => $_POST['degree_level'] ?? 'bachelor',
            'duration_years'        => (int)($_POST['duration_years'] ?? 4),
            'description'           => trim($_POST['description'] ?? ''),
            'admission_requirements'=> trim($_POST['admission_requirements'] ?? ''),
            'min_aggregate'         => !empty($_POST['min_aggregate']) ? (int)$_POST['min_aggregate'] : null,
            'tuition_local'         => !empty($_POST['tuition_local']) ? (float)$_POST['tuition_local'] : null,
            'application_deadline'  => !empty($_POST['application_deadline']) ? $_POST['application_deadline'] : null,
            'is_active'             => isset($_POST['is_active']) ? 1 : 0,
        ];
        if (empty($data['name'])) {
            $this->flash('errors', ['Programme name is required.']);
            $this->redirect(APP_URL . '/admin/universities/' . $id . '/programmes/create');
            return;
        }
        $cols = implode(', ', array_keys($data));
        $placeholders = implode(', ', array_fill(0, count($data), '?'));
        $stmt = $db->prepare("INSERT INTO programs ({$cols}) VALUES ({$placeholders})");
        $stmt->execute(array_values($data));
        $this->flash('success', 'Programme added successfully!');
        $this->redirect(APP_URL . '/admin/universities/' . $id . '/programmes');
    }

    // ── GET /admin/universities/{id}/programmes/{pid}/edit ────────
    public function editProgrammeForm(string $id, string $pid): void {
        $university = $this->uniModel->find((int)$id);
        if (!$university) { $this->abort(404); return; }
        $db = Database::getInstance();
        $stmt = $db->prepare("SELECT * FROM programs WHERE id = ? AND university_id = ?");
        $stmt->execute([(int)$pid, (int)$id]);
        $programme = $stmt->fetch();
        if (!$programme) { $this->abort(404); return; }
        $faculties = $db->prepare("SELECT * FROM faculties WHERE university_id = ? ORDER BY name");
        $faculties->execute([(int)$id]);
        $this->view('admin.programme_form', [
            'title'      => 'Edit Programme — ' . $programme['name'],
            'university' => $university,
            'programme'  => $programme,
            'faculties'  => $faculties->fetchAll(),
            'errors'     => $this->getFlash('errors') ?? [],
        ]);
    }

    // ── POST /admin/universities/{id}/programmes/{pid}/edit ───────
    public function editProgramme(string $id, string $pid): void {
        CSRF::check();
        $db = Database::getInstance();
        $data = [
            'faculty_id'            => !empty($_POST['faculty_id']) ? (int)$_POST['faculty_id'] : null,
            'name'                  => trim($_POST['name'] ?? ''),
            'degree_level'          => $_POST['degree_level'] ?? 'bachelor',
            'duration_years'        => (int)($_POST['duration_years'] ?? 4),
            'description'           => trim($_POST['description'] ?? ''),
            'admission_requirements'=> trim($_POST['admission_requirements'] ?? ''),
            'min_aggregate'         => !empty($_POST['min_aggregate']) ? (int)$_POST['min_aggregate'] : null,
            'tuition_local'         => !empty($_POST['tuition_local']) ? (float)$_POST['tuition_local'] : null,
            'application_deadline'  => !empty($_POST['application_deadline']) ? $_POST['application_deadline'] : null,
            'is_active'             => isset($_POST['is_active']) ? 1 : 0,
        ];
        $sets = implode(', ', array_map(fn($k) => "{$k} = ?", array_keys($data)));
        $vals = array_values($data);
        $vals[] = (int)$pid;
        $vals[] = (int)$id;
        $db->prepare("UPDATE programs SET {$sets} WHERE id = ? AND university_id = ?")->execute($vals);
        $this->flash('success', 'Programme updated!');
        $this->redirect(APP_URL . '/admin/universities/' . $id . '/programmes');
    }

    // ── POST /admin/universities/{id}/programmes/{pid}/delete ─────
    public function deleteProgramme(string $id, string $pid): void {
        CSRF::check();
        $db = Database::getInstance();
        $db->prepare("DELETE FROM programs WHERE id = ? AND university_id = ?")->execute([(int)$pid, (int)$id]);
        $this->flash('success', 'Programme deleted.');
        $this->redirect(APP_URL . '/admin/universities/' . $id . '/programmes');
    }


    public function scholarships(): void {
        $page   = max(1,(int)($_GET['page']??1));
        $result = $this->schModel->paginate($page, ITEMS_PER_PAGE, '', [], 'created_at DESC');
        $this->view('admin.scholarships', [
            'title'        => 'Manage Scholarships — Admin — BOAFO',
            'scholarships' => $result['items'],
            'pagination'   => $result,
        ]);
    }

    // ── GET /admin/scholarships/create ───────────────────────────
    public function createScholarshipForm(): void {
        $db = Database::getInstance();
        $universities = $db->query("SELECT id, name FROM universities ORDER BY name")->fetchAll();
        $this->view('admin.scholarship_form', [
            'title'        => 'Add Scholarship — Admin — BOAFO',
            'action'       => 'create',
            'scholarship'  => [],
            'universities' => $universities,
        ]);
    }

    // ── POST /admin/scholarships/create ──────────────────────────
    public function createScholarship(): void {
        CSRF::check();
        $data = $this->collectScholarshipData();
        $id   = $this->schModel->createWithSlug($data);
        // Link to universities
        $uniIds = $_POST['university_ids'] ?? [];
        $db = Database::getInstance();
        foreach ($uniIds as $uniId) {
            $db->prepare("INSERT IGNORE INTO scholarship_universities (scholarship_id, university_id) VALUES (?, ?)")
               ->execute([$id, (int)$uniId]);
        }
        $this->logAudit('create_scholarship', 'scholarships', $id, $data);
        $this->flash('success', 'Scholarship created!');
        $this->redirect(APP_URL . '/admin/scholarships');
    }

    // ── GET /admin/scholarships/{id}/edit ────────────────────────
    public function editScholarshipForm(string $id): void {
        $scholarship = $this->schModel->find((int)$id);
        if (!$scholarship) { http_response_code(404); return; }
        $db = Database::getInstance();
        $universities = $db->query("SELECT id, name FROM universities ORDER BY name")->fetchAll();
        $linked = $db->prepare("SELECT university_id FROM scholarship_universities WHERE scholarship_id = ?");
        $linked->execute([(int)$id]);
        $linkedIds = array_column($linked->fetchAll(), 'university_id');
        $this->view('admin.scholarship_form', [
            'title'        => 'Edit Scholarship — Admin — BOAFO',
            'action'       => 'edit',
            'scholarship'  => $scholarship,
            'universities' => $universities,
            'linked_ids'   => $linkedIds,
        ]);
    }

    // ── POST /admin/scholarships/{id}/edit ───────────────────────
    public function editScholarship(string $id): void {
        CSRF::check();
        $data = $this->collectScholarshipData();
        $this->schModel->update((int)$id, $data);
        // Re-link universities
        $db = Database::getInstance();
        $db->prepare("DELETE FROM scholarship_universities WHERE scholarship_id = ?")->execute([(int)$id]);
        $uniIds = $_POST['university_ids'] ?? [];
        foreach ($uniIds as $uniId) {
            $db->prepare("INSERT IGNORE INTO scholarship_universities (scholarship_id, university_id) VALUES (?, ?)")
               ->execute([(int)$id, (int)$uniId]);
        }
        $this->logAudit('edit_scholarship', 'scholarships', (int)$id, $data);
        $this->flash('success', 'Scholarship updated!');
        $this->redirect(APP_URL . '/admin/scholarships');
    }

    // ── POST /admin/scholarships/{id}/delete ─────────────────────
    public function deleteScholarship(string $id): void {
        CSRF::check();
        $this->schModel->delete((int)$id);
        $this->logAudit('delete_scholarship', 'scholarships', (int)$id);
        if ($this->isAjax()) $this->json(['success'=>true]);
        $this->flash('success','Scholarship deleted.');
        $this->redirect(APP_URL . '/admin/scholarships');
    }

    // ── GET /admin/mentors ────────────────────────────────────────
    public function mentors(): void {
        $db   = Database::getInstance();
        $page = max(1,(int)($_GET['page']??1));
        $offset = ($page - 1) * ITEMS_PER_PAGE;

        $total = (int)$db->query("SELECT COUNT(*) FROM mentor_profiles")->fetchColumn();
        $stmt  = $db->prepare(
            "SELECT mp.*, u.first_name, u.last_name, u.email, u.status AS user_status, u.created_at,
                    uni.name AS university_name
             FROM mentor_profiles mp
             JOIN users u ON u.id = mp.user_id
             LEFT JOIN universities uni ON uni.id = mp.university_id
             ORDER BY mp.verified ASC, u.created_at DESC
             LIMIT ? OFFSET ?"
        );
        $stmt->execute([ITEMS_PER_PAGE, $offset]);

        $this->view('admin.mentors', [
            'title'      => 'Manage Mentors — Admin — BOAFO',
            'mentors'    => $stmt->fetchAll(),
            'pagination' => ['total'=>$total,'total_pages'=>ceil($total/ITEMS_PER_PAGE),'current_page'=>$page,'has_prev'=>$page>1,'has_next'=>$page<ceil($total/ITEMS_PER_PAGE)],
        ]);
    }

    // ── POST /admin/mentors/{id}/verify ──────────────────────────
    public function verifyMentor(string $id): void {
        CSRF::check();
        $db = Database::getInstance();
        $db->prepare("UPDATE mentor_profiles SET verified = 1 WHERE user_id = ?")->execute([(int)$id]);
        $db->prepare("UPDATE users SET status = 'active' WHERE id = ?")->execute([(int)$id]);
        $this->logAudit('verify_mentor', 'mentor_profiles', (int)$id);
        if ($this->isAjax()) $this->json(['success'=>true]);
        $this->flash('success', 'Mentor verified!');
        $this->redirect(APP_URL . '/admin/mentors');
    }

    // ── GET /admin/analytics ──────────────────────────────────────
    public function analytics(): void {
        $db = Database::getInstance();
        $range = (int)($this->input('range', 30));
        if (!in_array($range, [7, 30, 90, 365])) $range = 30;

        $analytics = [];
        
        // Total Users
        $analytics['total_users'] = $db->query("SELECT COUNT(*) FROM users")->fetchColumn();
        
        // Active Users Pct (Assuming login within last 30 days)
        $activeUsers = $db->query("SELECT COUNT(*) FROM users WHERE last_login >= DATE_SUB(NOW(), INTERVAL 30 DAY)")->fetchColumn();
        $analytics['active_users_pct'] = $analytics['total_users'] > 0 ? round(($activeUsers / $analytics['total_users']) * 100) : 0;

        // Sessions This Period & Avg Duration
        $sessStats = $db->query("SELECT COUNT(*) as cnt, AVG(duration_min) as avg_dur FROM sessions WHERE created_at >= DATE_SUB(NOW(), INTERVAL $range DAY)")->fetch();
        $analytics['sessions_period'] = $sessStats['cnt'] ?? 0;
        $analytics['avg_session_duration'] = $sessStats['avg_dur'] ?? 0;

        // Messages Sent
        $analytics['messages_period'] = $db->query("SELECT COUNT(*) FROM messages WHERE created_at >= DATE_SUB(NOW(), INTERVAL $range DAY)")->fetchColumn();

        // Scholarship Apps (Using saved_scholarships as proxy)
        $analytics['scholarship_apps'] = $db->query("SELECT COUNT(*) FROM saved_scholarships WHERE created_at >= DATE_SUB(NOW(), INTERVAL $range DAY)")->fetchColumn();
        $analytics['scholarship_success_rate'] = 0; // Placeholder

        // Career Assessments
        $analytics['career_assessments'] = $db->query("SELECT COUNT(*) FROM career_assessments WHERE created_at >= DATE_SUB(NOW(), INTERVAL $range DAY)")->fetchColumn();

        // Resources Downloaded / Views
        $analytics['resource_downloads'] = $db->query("SELECT SUM(views) FROM resources")->fetchColumn() ?? 0;

        // Trends
        $analytics['session_trend'] = $db->query(
            "SELECT DATE(created_at) as date, COUNT(*) as count 
             FROM sessions 
             WHERE created_at >= DATE_SUB(NOW(), INTERVAL $range DAY) 
             GROUP BY DATE(created_at) ORDER BY date"
        )->fetchAll();

        $analytics['message_trend'] = $db->query(
            "SELECT DATE(created_at) as date, COUNT(*) as count 
             FROM messages 
             WHERE created_at >= DATE_SUB(NOW(), INTERVAL $range DAY) 
             GROUP BY DATE(created_at) ORDER BY date"
        )->fetchAll();

        // Top Scholarships
        $analytics['top_scholarships'] = $db->query(
            "SELECT s.name, COUNT(ss.user_id) as app_count 
             FROM scholarships s 
             LEFT JOIN saved_scholarships ss ON ss.scholarship_id = s.id 
             GROUP BY s.id ORDER BY app_count DESC LIMIT 5"
        )->fetchAll();

        // Top Mentors
        $analytics['top_mentors'] = $db->query(
            "SELECT u.first_name, u.last_name, CONCAT(u.first_name, ' ', u.last_name) as full_name, 
                    COUNT(s.id) as session_count, 
                    COUNT(DISTINCT s.student_id) as student_count,
                    COALESCE(AVG(s.rating), 0) as avg_rating
             FROM users u 
             JOIN sessions s ON s.mentor_id = u.id 
             WHERE u.role = 'mentor' AND s.created_at >= DATE_SUB(NOW(), INTERVAL $range DAY)
             GROUP BY u.id ORDER BY session_count DESC LIMIT 5"
        )->fetchAll();

        // Region Distribution
        $analytics['region_distribution'] = $db->query(
            "SELECT r.name as region, COUNT(sp.id) as count 
             FROM regions r 
             JOIN student_profiles sp ON sp.region_id = r.id 
             GROUP BY r.id ORDER BY count DESC LIMIT 10"
        )->fetchAll();

        // Top Careers
        $analytics['top_careers'] = []; 

        $this->view('admin.analytics', [
            'title'     => 'Analytics — Admin — BOAFO',
            'analytics' => $analytics,
            'range'     => $range
        ]);
    }

    // ── GET /admin/resources ─────────────────────────────────────
    public function resources(): void {
        $db   = Database::getInstance();
        $stmt = $db->query(
            "SELECT r.*, u.first_name, u.last_name
             FROM resources r LEFT JOIN users u ON u.id = r.author_id
             ORDER BY r.created_at DESC"
        );
        $this->view('admin.resources', [
            'title'     => 'Manage Resources — Admin — BOAFO',
            'resources' => $stmt->fetchAll(),
        ]);
    }

    public function createResourceForm(): void {
        $this->view('admin.resource_form', ['title' => 'Add Resource — Admin — BOAFO', 'resource' => []]);
    }

    public function createResource(): void {
        CSRF::check();
        $data = [
            'title'        => $this->sanitize($this->input('title', '')),
            'slug'         => slugify($this->input('title','')) . '-' . uniqid(),
            'category'     => $this->input('category', 'article'),
            'module'       => $this->input('module', 'general'),
            'description'  => $this->sanitize($this->input('description', '')),
            'content'      => $this->input('content', ''),
            'external_url' => $this->sanitize($this->input('external_url', '')),
            'author_id'    => Auth::id(),
            'is_featured'  => (int)$this->input('is_featured', 0),
            'is_published' => (int)$this->input('is_published', 0),
        ];

        $allowed = array_merge(ALLOWED_IMAGE_TYPES, ALLOWED_DOC_TYPES);
        if (!empty($_FILES['file']['name'])) {
            $file = uploadFile($_FILES['file'], 'resources', $allowed);
            if ($file) $data['file_path'] = $file;
        }

        if (!empty($_FILES['thumbnail']['name'])) {
            $thumbnail = uploadFile($_FILES['thumbnail'], 'thumbnails', ALLOWED_IMAGE_TYPES);
            if ($thumbnail) $data['thumbnail'] = $thumbnail;
        }

        $db = Database::getInstance();
        $cols = implode(', ', array_keys($data));
        $phs  = implode(', ', array_fill(0, count($data), '?'));
        $db->prepare("INSERT INTO resources ({$cols}) VALUES ({$phs})")->execute(array_values($data));

        $this->flash('success', 'Resource created!');
        $this->redirect(APP_URL . '/admin/resources');
    }

    // ── GET /admin/resources/{id}/edit ───────────────────────────
    public function editResourceForm(string $id): void {
        $db = Database::getInstance();
        $stmt = $db->prepare("SELECT * FROM resources WHERE id = ?");
        $stmt->execute([(int)$id]);
        $resource = $stmt->fetch();
        if (!$resource) { http_response_code(404); return; }
        $this->view('admin.resource_form', [
            'title'    => 'Edit Resource — Admin — BOAFO',
            'resource' => $resource,
        ]);
    }

    // ── POST /admin/resources/{id}/edit ──────────────────────────
    public function editResource(string $id): void {
        CSRF::check();
        $data = [
            'title'        => $this->sanitize($this->input('title', '')),
            'category'     => $this->input('category', 'article'),
            'module'       => $this->input('module', 'general'),
            'description'  => $this->sanitize($this->input('description', '')),
            'content'      => $this->input('content', ''),
            'external_url' => $this->sanitize($this->input('external_url', '')),
            'is_featured'  => (int)$this->input('is_featured', 0),
            'is_published' => (int)$this->input('is_published', 0),
        ];

        $allowed = array_merge(ALLOWED_IMAGE_TYPES, ALLOWED_DOC_TYPES);
        if (!empty($_FILES['file']['name'])) {
            $file = uploadFile($_FILES['file'], 'resources', $allowed);
            if ($file) $data['file_path'] = $file;
        }

        if (!empty($_FILES['thumbnail']['name'])) {
            $thumbnail = uploadFile($_FILES['thumbnail'], 'thumbnails', ALLOWED_IMAGE_TYPES);
            if ($thumbnail) $data['thumbnail'] = $thumbnail;
        }

        $db = Database::getInstance();
        $sets = [];
        $vals = [];
        foreach ($data as $k => $v) {
            $sets[] = "$k = ?";
            $vals[] = $v;
        }
        $vals[] = (int)$id;
        $db->prepare("UPDATE resources SET " . implode(', ', $sets) . " WHERE id = ?")->execute($vals);

        $this->flash('success', 'Resource updated successfully!');
        $this->redirect(APP_URL . '/admin/resources');
    }

    // ── POST /admin/resources/{id}/delete ────────────────────────
    public function deleteResource(string $id): void {
        CSRF::check();
        $db = Database::getInstance();
        $db->prepare("DELETE FROM resources WHERE id = ?")->execute([(int)$id]);
        $this->logAudit('delete_resource', 'resources', (int)$id);

        $this->flash('success', 'Resource deleted.');
        $this->redirect(APP_URL . '/admin/resources');
    }

    // ── GET /admin/audit-logs ─────────────────────────────────────
    public function auditLogs(): void {
        $db   = Database::getInstance();
        $logs = $db->query(
            "SELECT al.*, u.first_name, u.last_name, u.email
             FROM audit_logs al LEFT JOIN users u ON u.id = al.user_id
             ORDER BY al.created_at DESC LIMIT 100"
        )->fetchAll();
        $this->view('admin.audit_logs', [
            'title' => 'Audit Logs — Admin — BOAFO',
            'logs'  => $logs,
        ]);
    }

    // ── GET /admin/users/create ──────────────────────────────────
    public function createUserForm(): void {
        $this->view('admin.user_form', [
            'title' => 'Add User — Admin — BOAFO',
            'user'  => [],
        ]);
    }

    // ── POST /admin/users/create ─────────────────────────────────
    public function createUser(): void {
        CSRF::check();
        $email    = trim($this->input('email', ''));
        $role     = $this->input('role', 'student');
        $status   = $this->input('status', 'active');
        $password = $this->input('password', '');
        $passwordConfirm = $this->input('password_confirm', '');

        $errors = $this->validate([
            'first_name' => 'required',
            'last_name'  => 'required',
            'email'      => 'required|email',
            'password'   => 'required|min:8',
            'role'       => 'required',
        ]);

        if ($password !== $passwordConfirm) {
            $errors['password_confirm'] = 'Passwords do not match.';
        }

        if ($this->userModel->findByEmail($email)) {
            $errors['email'] = 'This email is already registered.';
        }

        if (!empty($errors)) {
            $this->flash('error', array_values($errors)[0]);
            $this->redirect(APP_URL . '/admin/users/create');
        }

        // Admin-created users bypass the normal createUser() which forces pending status.
        $db = Database::getInstance();
        $db->prepare(
            "INSERT INTO users (uuid, first_name, last_name, email, phone, password_hash, role, status, email_verified, verify_token, created_at, updated_at)
             VALUES (UUID(), ?, ?, ?, ?, ?, ?, ?, ?, NULL, NOW(), NOW())"
        )->execute([
            trim($this->input('first_name', '')),
            trim($this->input('last_name', '')),
            $email,
            trim($this->input('phone', '')),
            password_hash($password, PASSWORD_BCRYPT, ['cost' => 12]),
            $role,
            $status,
            (int)$this->input('email_verified', 1),
        ]);
        $userId = (int)$db->lastInsertId();

        $this->createRoleProfile($userId, $role);
        $this->logAudit('create_user', 'users', $userId, ['email' => $email, 'role' => $role]);

        $this->flash('success', 'User account created successfully!');
        $this->redirect(APP_URL . '/admin/users');
    }

    // ── GET /admin/users/{id}/edit ───────────────────────────────
    public function editUserForm(string $id): void {
        $user = $this->userModel->find((int)$id);
        if (!$user) { http_response_code(404); return; }
        $this->view('admin.user_form', [
            'title' => 'Edit User — Admin — BOAFO',
            'user'  => $user,
        ]);
    }

    // ── POST /admin/users/{id}/edit ──────────────────────────────
    public function editUser(string $id): void {
        CSRF::check();
        $user = $this->userModel->find((int)$id);
        if (!$user) { http_response_code(404); return; }

        $email    = trim($this->input('email', ''));
        $role     = $this->input('role', 'student');
        $status   = $this->input('status', 'active');
        $password = $this->input('password', '');
        $passwordConfirm = $this->input('password_confirm', '');

        $errors = $this->validate([
            'first_name' => 'required',
            'last_name'  => 'required',
            'email'      => 'required|email',
            'role'       => 'required',
        ]);

        $existing = $this->userModel->findByEmail($email);
        if ($existing && (int)$existing['id'] !== (int)$id) {
            $errors['email'] = 'This email is already registered to another user.';
        }

        if (!empty($password)) {
            if (strlen($password) < 8) {
                $errors['password'] = 'Password must be at least 8 characters.';
            }
            if ($password !== $passwordConfirm) {
                $errors['password_confirm'] = 'Passwords do not match.';
            }
        }

        if (!empty($errors)) {
            $this->flash('error', array_values($errors)[0]);
            $this->redirect(APP_URL . "/admin/users/{$id}/edit");
        }

        // Only update columns that exist in the users table
        $data = [
            'first_name'     => trim($this->input('first_name', '')),
            'last_name'      => trim($this->input('last_name', '')),
            'email'          => $email,
            'phone'          => trim($this->input('phone', '')),
            'role'           => $role,
            'status'         => $status,
            'email_verified' => (int)$this->input('email_verified', 0),
        ];

        if (!empty($password)) {
            $data['password_hash'] = password_hash($password, PASSWORD_BCRYPT, ['cost' => 12]);
        }

        $this->userModel->update((int)$id, $data);
        $this->logAudit('edit_user', 'users', (int)$id, ['email' => $email, 'role' => $role]);

        $this->flash('success', 'User account updated!');
        $this->redirect(APP_URL . '/admin/users');
    }

    // ── POST /admin/users/{id}/toggle ────────────────────────────
    public function toggleUserStatus(string $id): void {
        CSRF::check();
        $user = $this->userModel->find((int)$id);
        if (!$user) { http_response_code(404); return; }

        $newStatus = $user['status'] === 'active' ? 'inactive' : 'active';
        $this->userModel->update((int)$id, ['status' => $newStatus]);
        $this->logAudit('toggle_user_status', 'users', (int)$id, ['status' => $newStatus]);

        $this->flash('success', "User account status toggled to {$newStatus}!");
        $this->redirect(APP_URL . '/admin/users');
    }

    // ── POST /admin/users/{id} (DELETE) ──────────────────────────
    public function deleteUser(string $id): void {
        CSRF::check();
        $this->userModel->delete((int)$id);
        $this->logAudit('delete_user', 'users', (int)$id);

        $this->flash('success', 'User account deleted.');
        $this->redirect(APP_URL . '/admin/users');
    }

    // ── POST /admin/users/{id}/notes ─────────────────────────────
    public function saveUserNotes(string $id): void {
        CSRF::check();
        $notes = $this->input('notes', '');
        // admin_notes column does not exist in users table — store as audit log entry
        $db = Database::getInstance();
        $db->prepare(
            "INSERT INTO audit_logs (user_id, action, entity_type, entity_id, new_values, ip_address, user_agent) VALUES (?, 'admin_note', 'users', ?, ?, ?, ?)"
        )->execute([
            Auth::id(), (int)$id,
            json_encode(['notes' => $notes]),
            $_SERVER['REMOTE_ADDR'] ?? '',
            $_SERVER['HTTP_USER_AGENT'] ?? '',
        ]);

        $this->flash('success', 'Admin note saved.');
        $this->redirect(APP_URL . '/admin/users/' . $id);
    }

    // ── POST /admin/users/{id}/approve ───────────────────────────
    public function approveUser(string $id): void {
        CSRF::check();
        $db = Database::getInstance();
        $db->prepare("UPDATE mentor_profiles SET verified = 1 WHERE user_id = ?")->execute([(int)$id]);
        $db->prepare("UPDATE users SET status = 'active' WHERE id = ?")->execute([(int)$id]);
        $this->logAudit('approve_mentor', 'mentor_profiles', (int)$id);

        $this->flash('success', 'Mentor approved and account activated!');
        $this->redirect(APP_URL . '/admin/users/' . $id);
    }

    // ── POST /admin/users/{id}/reject ────────────────────────────
    public function rejectUser(string $id): void {
        CSRF::check();
        $db = Database::getInstance();
        $db->prepare("UPDATE mentor_profiles SET verified = 0 WHERE user_id = ?")->execute([(int)$id]);
        $db->prepare("UPDATE users SET status = 'inactive' WHERE id = ?")->execute([(int)$id]);
        $this->logAudit('reject_mentor', 'mentor_profiles', (int)$id);

        $this->flash('success', 'Mentor approval rejected.');
        $this->redirect(APP_URL . '/admin/users/' . $id);
    }

    // ── POST /admin/users/bulk ───────────────────────────────────
    public function bulkAction(): void {
        CSRF::check();
        $action = $this->input('action', '');
        $idsStr = $this->input('user_ids', '');
        if (empty($idsStr)) {
            $this->flash('error', 'No users selected.');
            $this->redirect(APP_URL . '/admin/users');
        }

        $ids = array_map('intval', explode(',', $idsStr));
        $db = Database::getInstance();

        if ($action === 'activate') {
            foreach ($ids as $id) {
                $this->userModel->update($id, ['status' => 'active']);
            }
            $this->flash('success', count($ids) . ' users activated.');
        } elseif ($action === 'deactivate') {
            foreach ($ids as $id) {
                $this->userModel->update($id, ['status' => 'inactive']);
            }
            $this->flash('success', count($ids) . ' users deactivated.');
        } elseif ($action === 'delete') {
            foreach ($ids as $id) {
                $this->userModel->delete($id);
            }
            $this->flash('success', count($ids) . ' users deleted.');
        }

        $this->redirect(APP_URL . '/admin/users');
    }

    private function createRoleProfile(int $userId, string $role): void {
        $db = Database::getInstance();
        if ($role === 'student') {
            $db->prepare("INSERT IGNORE INTO student_profiles (user_id) VALUES (?)")->execute([$userId]);
        } elseif ($role === 'mentor') {
            $db->prepare("INSERT IGNORE INTO mentor_profiles (user_id) VALUES (?)")->execute([$userId]);
        } elseif ($role === 'tutor') {
            $db->prepare("INSERT IGNORE INTO tutor_profiles (user_id) VALUES (?)")->execute([$userId]);
        }
    }

    // ── PRIVATE HELPERS ───────────────────────────────────────────
    private function collectUniversityData(): array {
        return [
            'name'               => $this->sanitize($this->input('name', '')),
            'country_id'         => (int)$this->input('country_id', 1),
            'region_id'          => $this->input('region_id') ?: null,
            'city'               => $this->sanitize($this->input('city', '')),
            'university_type'    => $this->input('university_type', 'public'),
            'website'            => $this->sanitize($this->input('website', '')),
            'description'        => $this->sanitize($this->input('description', '')),
            'established_year'   => $this->input('established_year') ?: null,
            'student_population' => $this->input('student_population') ?: null,
            'ranking_local'      => $this->input('ranking_local') ?: null,
            'accreditation'      => $this->sanitize($this->input('accreditation', '')),
            'contact_email'      => $this->sanitize($this->input('contact_email', '')),
            'contact_phone'      => $this->sanitize($this->input('contact_phone', '')),
            'contact_address'    => $this->sanitize($this->input('contact_address', '')),
            'application_portal' => $this->sanitize($this->input('application_portal', '')),
            'is_featured'        => (int)$this->input('is_featured', 0),
            'status'             => $this->input('status', 'active'),
        ];
    }

    private function collectScholarshipData(): array {
        return [
            'name'                 => $this->sanitize($this->input('name', '')),
            'provider'             => $this->sanitize($this->input('provider', '')),
            'category'             => $this->input('category', 'general'),
            'scholarship_type'     => $this->input('scholarship_type', 'full'),
            'coverage'             => $this->sanitize($this->input('coverage', '')),
            'value_amount'         => $this->input('value_amount') ?: null,
            'num_beneficiaries'    => $this->input('num_beneficiaries') ?: null,
            'num_applicants_est'   => $this->input('num_applicants_est') ?: null,
            'description'          => $this->sanitize($this->input('description', '')),
            'eligibility_requirements'=> $this->sanitize($this->input('eligibility_requirements', '')),
            'required_documents'   => $this->sanitize($this->input('required_documents', '')),
            'application_deadline' => $this->input('application_deadline') ?: null,
            'min_aggregate'        => $this->input('min_aggregate') ?: null,
            'financial_need'       => (int)$this->input('financial_need', 0),
            'gender_restriction'   => $this->input('gender_restriction', 'all'),
            'application_link'     => $this->sanitize($this->input('application_link', '')),
            'contact_email'        => $this->sanitize($this->input('contact_email', '')),
            'is_featured'          => (int)$this->input('is_featured', 0),
            'status'               => $this->input('status', 'open'),
            'is_active'            => 1,
        ];
    }

    private function logAudit(string $action, string $entity, int $entityId, array $data = []): void {
        $db = Database::getInstance();
        $db->prepare(
            "INSERT INTO audit_logs (user_id, action, entity_type, entity_id, new_values, ip_address, user_agent) VALUES (?, ?, ?, ?, ?, ?, ?)"
        )->execute([
            Auth::id(), $action, $entity, $entityId,
            json_encode($data),
            $_SERVER['REMOTE_ADDR'] ?? '',
            $_SERVER['HTTP_USER_AGENT'] ?? ''
        ]);
    }
}
