<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class Course_model extends CI_Model {
    
    public function get_eligible_courses($specialization, $marks) {
        // Use query caching for performance
        $this->db->cache_on();
        
        $this->db->select('id, course_code, course_name, description, min_marks, available_seats, duration, fees');
        $this->db->from('courses');
        $this->db->where('specialization', $specialization);
        $this->db->where('min_marks <=', $marks);
        $this->db->where('available_seats >', 0);
        $this->db->where('status', 'active');
        $this->db->order_by('min_marks', 'DESC');
        
        $query = $this->db->get();
        $this->db->cache_off();
        
        return $query->result();
    }
    
    public function get_course_with_stats($course_id) {
        $this->db->select('c.*, 
            (SELECT COUNT(*) FROM applications WHERE course_id = c.id AND status = "approved") as approved_count,
            (SELECT COUNT(*) FROM applications WHERE course_id = c.id AND status = "pending") as pending_count');
        $this->db->from('courses c');
        $this->db->where('c.id', $course_id);
        return $this->db->get()->row();
    }
    
    public function update_seat_count($course_id, $change) {
        $this->db->set('available_seats', "GREATEST(0, available_seats + {$change})", FALSE);
        $this->db->where('id', $course_id);
        $this->db->update('courses');
        
        // Update status if seats are full
        $this->db->query("UPDATE courses SET status = CASE WHEN available_seats = 0 THEN 'full' ELSE 'active' END WHERE id = ?", array($course_id));
    }
    
    public function get_popular_courses($limit = 5) {
        $this->db->select('c.*, COUNT(a.id) as application_count');
        $this->db->from('courses c');
        $this->db->join('applications a', 'a.course_id = c.id', 'left');
        $this->db->group_by('c.id');
        $this->db->order_by('application_count', 'DESC');
        $this->db->limit($limit);
        return $this->db->get()->result();
    }
}