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

class Scholarship extends BaseModel {
    protected string $table = 'scholarships';

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

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

        if (!empty($filters['q'])) {
            $q = "%{$filters['q']}%";
            $where[]  = "(s.name LIKE ? OR s.provider LIKE ? OR s.description LIKE ?)";
            $params   = array_merge($params, [$q, $q, $q]);
        }
        if (!empty($filters['category'])) {
            $where[]  = "s.category = ?";
            $params[] = $filters['category'];
        }
        if (!empty($filters['type'])) {
            $where[]  = "s.scholarship_type = ?";
            $params[] = $filters['type'];
        }
        if (!empty($filters['status'])) {
            $where[]  = "s.status = ?";
            $params[] = $filters['status'];
        }
        if (isset($filters['financial_need']) && $filters['financial_need'] !== '') {
            $where[]  = "s.financial_need = ?";
            $params[] = $filters['financial_need'];
        }
        if (!empty($filters['university_id'])) {
            $where[]  = "EXISTS (SELECT 1 FROM scholarship_universities su WHERE su.scholarship_id = s.id AND su.university_id = ?)";
            $params[] = $filters['university_id'];
        }

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

        $countStmt = $this->db->prepare("SELECT COUNT(*) FROM scholarships s WHERE {$whereStr}");
        $countStmt->execute($params);
        $total = (int) $countStmt->fetchColumn();

        $sql = "SELECT s.*,
                    GROUP_CONCAT(DISTINCT u.name SEPARATOR ', ') AS linked_universities
                FROM scholarships s
                LEFT JOIN scholarship_universities su ON su.scholarship_id = s.id
                LEFT JOIN universities u ON u.id = su.university_id
                WHERE {$whereStr}
                GROUP BY s.id
                ORDER BY s.is_featured DESC, s.application_deadline ASC
                LIMIT ? OFFSET ?";

        $paginatedParams = array_merge($params, [ITEMS_PER_PAGE, $offset]);
        $stmt = $this->db->prepare($sql);
        $stmt->execute($paginatedParams);

        return [
            'items'        => $stmt->fetchAll(),
            '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 getWithLinkedUniversities(string $slug): ?array {
        $sch = $this->fetchOne("SELECT * FROM scholarships WHERE slug = ?", [$slug]);
        if (!$sch) return null;

        $sch['universities'] = $this->fetchAll(
            "SELECT u.* FROM scholarship_universities su
             JOIN universities u ON u.id = su.university_id
             WHERE su.scholarship_id = ?",
            [$sch['id']]
        );
        return $sch;
    }

    public function getFeatured(int $limit = 6): array {
        return $this->fetchAll(
            "SELECT * FROM scholarships WHERE is_featured = 1 AND is_active = 1 AND status = 'open'
             ORDER BY application_deadline ASC LIMIT ?",
            [$limit]
        );
    }

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

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

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

    public function getSavedByUser(int $userId): array {
        return $this->fetchAll(
            "SELECT s.*, ss.created_at AS saved_at
             FROM saved_scholarships ss
             JOIN scholarships s ON s.id = ss.scholarship_id
             WHERE ss.user_id = ?
             ORDER BY ss.created_at DESC",
            [$userId]
        );
    }

    public function getUpcomingDeadlines(int $limit = 5): array {
        return $this->fetchAll(
            "SELECT * FROM scholarships
             WHERE is_active = 1 AND status = 'open' AND application_deadline >= CURDATE()
             ORDER BY application_deadline ASC LIMIT ?",
            [$limit]
        );
    }

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