<?php
require_once APP_PATH . '/Core/BaseModel.php';

class University extends BaseModel {
    protected string $table = 'universities';

    public function findBySlug(string $slug): ?array {
        return $this->findBy('slug', $slug);
    }

    public function search(array $filters = [], int $page = 1): array {
        $where  = ['1=1'];
        $params = [];

        if (!empty($filters['q'])) {
            $where[]  = "(u.name LIKE ? OR u.description LIKE ? OR u.city LIKE ?)";
            $q = "%{$filters['q']}%";
            $params = array_merge($params, [$q, $q, $q]);
        }
        if (!empty($filters['country_id'])) {
            $where[]  = "u.country_id = ?";
            $params[] = $filters['country_id'];
        }
        if (!empty($filters['type'])) {
            $where[]  = "u.university_type = ?";
            $params[] = $filters['type'];
        }
        if (!empty($filters['region_id'])) {
            $where[]  = "u.region_id = ?";
            $params[] = $filters['region_id'];
        }

        $whereStr = implode(' AND ', $where);
        $offset   = ($page - 1) * ITEMS_PER_PAGE;

        $countSql = "SELECT COUNT(*) FROM universities u WHERE {$whereStr}";
        $stmt = $this->db->prepare($countSql);
        $stmt->execute($params);
        $total = (int) $stmt->fetchColumn();

        $sql = "SELECT u.*, c.name AS country_name, r.name AS region_name,
                       (SELECT COUNT(*) FROM programs WHERE university_id = u.id) AS program_count,
                       (SELECT COUNT(*) FROM mentor_profiles WHERE university_id = u.id) AS mentor_count
                FROM universities u
                LEFT JOIN countries c ON u.country_id = c.id
                LEFT JOIN regions r   ON u.region_id  = r.id
                WHERE {$whereStr}
                ORDER BY u.is_featured DESC, u.name ASC
                LIMIT ? OFFSET ?";
        $params[] = ITEMS_PER_PAGE;
        $params[] = $offset;
        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        $items = $stmt->fetchAll();

        return [
            'items'        => $items,
            'total'        => $total,
            'per_page'     => ITEMS_PER_PAGE,
            'current_page' => $page,
            'total_pages'  => (int) ceil($total / ITEMS_PER_PAGE),
            'has_prev'     => $page > 1,
            'has_next'     => $page < ceil($total / ITEMS_PER_PAGE),
        ];
    }

    public function getWithDetails(string $slug): ?array {
        return $this->fetchOne(
            "SELECT u.*, c.name AS country_name, r.name AS region_name
             FROM universities u
             LEFT JOIN countries c ON u.country_id = c.id
             LEFT JOIN regions r   ON u.region_id  = r.id
             WHERE u.slug = ? AND u.status = 'active'",
            [$slug]
        );
    }

    public function getPrograms(int $universityId, ?string $degree = null): array {
        $sql = "SELECT p.*, f.name AS faculty_name 
                FROM programs p
                LEFT JOIN faculties f ON p.faculty_id = f.id
                WHERE p.university_id = ? AND p.is_active = 1";
        $params = [$universityId];
        if ($degree) {
            $sql .= " AND p.degree_level = ?";
            $params[] = $degree;
        }
        $sql .= " ORDER BY f.name, p.name";
        return $this->fetchAll($sql, $params);
    }

    public function getMentors(int $universityId, int $limit = 6): array {
        return $this->fetchAll(
            "SELECT u.id, u.first_name, u.last_name, u.avatar,
                    mp.program, mp.faculty, mp.year_level, mp.rating,
                    mp.is_available, mp.expertise_areas
             FROM mentor_profiles mp
             JOIN users u ON u.id = mp.user_id
             WHERE mp.university_id = ? AND u.status = 'active' AND mp.verified = 1
             ORDER BY mp.rating DESC, mp.sessions_completed DESC
             LIMIT ?",
            [$universityId, $limit]
        );
    }

    public function getScholarships(int $universityId): array {
        return $this->fetchAll(
            "SELECT s.* FROM scholarships s
             JOIN scholarship_universities su ON su.scholarship_id = s.id
             WHERE su.university_id = ? AND s.is_active = 1
             ORDER BY s.is_featured DESC, s.application_deadline ASC",
            [$universityId]
        );
    }

    public function isSaved(int $userId, int $universityId): bool {
        $result = $this->fetchOne(
            "SELECT id FROM saved_universities WHERE user_id = ? AND university_id = ?",
            [$userId, $universityId]
        );
        return $result !== null;
    }

    public function save(int $userId, int $universityId): void {
        $this->execute(
            "INSERT IGNORE INTO saved_universities (user_id, university_id) VALUES (?, ?)",
            [$userId, $universityId]
        );
    }

    public function unsave(int $userId, int $universityId): void {
        $this->execute(
            "DELETE FROM saved_universities WHERE user_id = ? AND university_id = ?",
            [$userId, $universityId]
        );
    }

    public function getSavedByUser(int $userId): array {
        return $this->fetchAll(
            "SELECT u.*, c.name AS country_name, su.created_at AS saved_at
             FROM saved_universities su
             JOIN universities u ON u.id = su.university_id
             LEFT JOIN countries c ON u.country_id = c.id
             WHERE su.user_id = ?
             ORDER BY su.created_at DESC",
            [$userId]
        );
    }

    public function getFeatured(int $limit = 6): array {
        return $this->fetchAll(
            "SELECT u.*, c.name AS country_name,
                    (SELECT COUNT(*) FROM programs WHERE university_id = u.id) AS program_count
             FROM universities u
             LEFT JOIN countries c ON u.country_id = c.id
             WHERE u.is_featured = 1 AND u.status = 'active'
             LIMIT ?",
            [$limit]
        );
    }

    public function createWithSlug(array $data): int {
        $data['slug'] = slugify($data['name']) . '-' . uniqid();
        return $this->create($data);
    }

    public function getReviews(int $universityId): array {
        return $this->fetchAll(
            "SELECT r.*, u.first_name, u.last_name, u.avatar
             FROM university_reviews r
             JOIN users u ON u.id = r.user_id
             WHERE r.university_id = ? AND r.is_approved = 1
             ORDER BY r.created_at DESC",
            [$universityId]
        );
    }

    public function getAverageRating(int $universityId): float {
        $result = $this->fetchOne(
            "SELECT AVG(rating) AS avg_rating FROM university_reviews WHERE university_id = ? AND is_approved = 1",
            [$universityId]
        );
        return round((float) ($result['avg_rating'] ?? 0), 1);
    }
}
