<?php
class Report_model extends CI_Model {

    public function __construct() {
        parent::__construct();
        $this->load->database();
    }

    public function get_all_sports() {
        return $this->db->get('sports')->result();
    }

    public function get_report($start, $end, $sport='', $status='') {
        $this->db->select("b.*, c.name as customer_name, c.email, s.name as sport_name, p.name as pitch_name");
        $this->db->from("bookings b");
        $this->db->join("customer c","b.user_id = c.c_id","left");
        $this->db->join("pitches p","b.pitch_id = p.id","left");
        $this->db->join("sports s","p.sport_id = s.id","left");
        $this->db->where("DATE(b.booking_date) >=", $start);
        $this->db->where("DATE(b.booking_date) <=", $end);

        if ($sport) {
            $this->db->where("s.id", $sport);
        }

        if ($status) {
            $this->db->where("b.status", $status);
        }

        $this->db->order_by("b.booking_date","DESC");
        return $this->db->get()->result();
    }

    public function get_summary($start, $end, $sport='', $status='') {
        $this->db->select("COUNT(b.id) as total_bookings, SUM(b.total_price) as total_revenue");
        $this->db->from("bookings b");
        $this->db->join("pitches p","b.pitch_id = p.id","left");
        $this->db->join("sports s","p.sport_id = s.id","left");
        $this->db->where("DATE(b.booking_date) >=", $start);
        $this->db->where("DATE(b.booking_date) <=", $end);

        if ($sport) {
            $this->db->where("s.id", $sport);
        }

        if ($status) {
            $this->db->where("b.status", $status);
        }

        return $this->db->get()->row();
    }
}
?>
