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

class Discipline_model extends CI_Model {

    public function __construct() {
        parent::__construct();
        $this->current_session = $this->setting_model->getCurrentSession();
    }

    public function get_students_with_incidents() {
        $this->db->select('s.id, s.firstname, s.lastname, s.admission_no, c.class, sec.section, 
                          it.name as incident_type, ic.name as incident_category, 
                          COUNT(ir.id) as incident_count, MAX(ir.incident_date) as latest_incident');
        $this->db->from('students s');
        $this->db->join('incident_reports ir', 's.id = ir.student_id');
        $this->db->join('classes c', 'ir.class_id = c.id');
        $this->db->join('sections sec', 'ir.section_id = sec.id');
        $this->db->join('incident_types it', 'ir.incident_type_id = it.id');
        $this->db->join('incident_categories ic', 'ir.incident_category_id = ic.id');
        $this->db->group_by('s.id, it.name, ic.name');
        $this->db->order_by('latest_incident', 'DESC');
        $this->db->limit(20); // Limit to recent 20 records
        
        $query = $this->db->get();
        return $query->result_array();
    }

    // Incident Types Methods
    public function get_incident_types($id = null) {
        $this->db->select()->from('incident_types');
        if ($id != null) {
            $this->db->where('id', $id);
        } else {
            $this->db->order_by('id');
        }
        $query = $this->db->get();
        if ($id != null) {
            return $query->row_array();
        } else {
            return $query->result_array();
        }
    }

    public function get_incident_type($id) {
        $this->db->select()->from('incident_types');
        $this->db->where('id', $id);
        $query = $this->db->get();
        return $query->row_array();
    }

    public function add_incident_type($data) {
        if (isset($data['id'])) {
            $this->db->where('id', $data['id']);
            $this->db->update('incident_types', $data);
        } else {
            $this->db->insert('incident_types', $data);
            return $this->db->insert_id();
        }
    }

    public function delete_incident_type($id) {
        $this->db->where('id', $id);
        $this->db->delete('incident_types');
    }

    // Incident Categories Methods
    public function get_incident_categories($id = null) {
        $this->db->select()->from('incident_categories');
        if ($id != null) {
            $this->db->where('id', $id);
        } else {
            $this->db->order_by('id');
        }
        $query = $this->db->get();
        if ($id != null) {
            return $query->row_array();
        } else {
            return $query->result_array();
        }
    }

    // Incident Reports Methods
    public function add_incident_report($data) {
        $this->db->insert('incident_reports', $data);
        return $this->db->insert_id();
    }

    public function get_all_incident_reports() {
        $this->db->select('ir.*, s.firstname, s.lastname, s.admission_no, c.class, sec.section, it.name as incident_type, ic.name as incident_category');
        $this->db->from('incident_reports ir');
        $this->db->join('students s', 's.id = ir.student_id');
        $this->db->join('classes c', 'c.id = ir.class_id');
        $this->db->join('sections sec', 'sec.id = ir.section_id');
        $this->db->join('incident_types it', 'it.id = ir.incident_type_id');
        $this->db->join('incident_categories ic', 'ic.id = ir.incident_category_id');
        $this->db->order_by('ir.created_at', 'desc');
        $query = $this->db->get();
        return $query->result_array();
    }

    // Get filtered incident reports
    public function get_filtered_incident_reports($category_filter = null, $type_filter = null, $learner_filter = null) {
        $this->db->select('ir.*, s.firstname, s.lastname, s.admission_no, c.class, sec.section, it.name as incident_type, ic.name as incident_category');
        $this->db->from('incident_reports ir');
        $this->db->join('students s', 's.id = ir.student_id');
        $this->db->join('classes c', 'c.id = ir.class_id');
        $this->db->join('sections sec', 'sec.id = ir.section_id');
        $this->db->join('incident_types it', 'it.id = ir.incident_type_id');
        $this->db->join('incident_categories ic', 'ic.id = ir.incident_category_id');
        
        if ($category_filter) {
            $this->db->where('ir.incident_category_id', $category_filter);
        }
        
        if ($type_filter) {
            $this->db->where('ir.incident_type_id', $type_filter);
        }
        
        if ($learner_filter) {
            $this->db->where('ir.student_id', $learner_filter);
        }
        
        $this->db->order_by('ir.incident_date', 'desc');
        $query = $this->db->get();
        return $query->result_array();
    }

    // Get all students for filter dropdown
    public function get_all_students() {
        $this->db->select('s.id, s.firstname, s.lastname, s.admission_no, c.class, sec.section');
        $this->db->from('students s');
        $this->db->join('student_session ss', 'ss.student_id = s.id');
        $this->db->join('classes c', 'c.id = ss.class_id');
        $this->db->join('sections sec', 'sec.id = ss.section_id');
        $this->db->where('ss.session_id', $this->current_session);
        $this->db->order_by('s.firstname', 'asc');
        $query = $this->db->get();
        return $query->result_array();
    }

    public function get_incident_report($id) {
        $this->db->select('ir.*, s.firstname, s.lastname, s.admission_no, c.class, sec.section, it.name as incident_type, ic.name as incident_category');
        $this->db->from('incident_reports ir');
        $this->db->join('students s', 's.id = ir.student_id');
        $this->db->join('classes c', 'c.id = ir.class_id');
        $this->db->join('sections sec', 'sec.id = ir.section_id');
        $this->db->join('incident_types it', 'it.id = ir.incident_type_id');
        $this->db->join('incident_categories ic', 'ic.id = ir.incident_category_id');
        $this->db->where('ir.id', $id);
        $query = $this->db->get();
        return $query->row_array();
    }

    public function delete_incident_report($id) {
        $this->db->where('id', $id);
        $this->db->delete('incident_reports');
    }

    public function get_incident_category($id) {
        $this->db->select()->from('incident_categories');
        $this->db->where('id', $id);
        $query = $this->db->get();
        return $query->row_array();
    }

    public function add_incident_category($data) {
        if (isset($data['id'])) {
            $this->db->where('id', $data['id']);
            $this->db->update('incident_categories', $data);
        } else {
            $this->db->insert('incident_categories', $data);
            return $this->db->insert_id();
        }
    }

    public function delete_incident_category($id) {
        $this->db->where('id', $id);
        $this->db->delete('incident_categories');
    }
}
?>