<?php
class Admin{
    
    public $username;
    public $password;
    
    public $reportStart;
    public $reportEnd;
    
    public $sessionID;
    
    public $coachID;
    
    public $invID;
    public $invNo;
    public $requestedCourseList;
    
    public $tableName;
    
    // database connection
    private $conn;
  
    // constructor with $db as database connection
    public function __construct($db){
        $this->conn = $db;
    }
    
    //client Details
    function getClientDetails(){
        
        $query = "SELECT * FROM admin WHERE username = '".$this->username."' AND password = '".$this->password."' ";
        $result = mysqli_query($this->conn,$query);
        
        if($result){
            // select all query
            $query2 = "SELECT * FROM clientProfile";
            $result2 = mysqli_query($this->conn,$query2);
            $row2 = mysqli_fetch_all($result2,MYSQLI_ASSOC);
            return $row2;
        }else{
            $row = mysqli_fetch_all($result,MYSQLI_ASSOC);
            return $row;
        }
    }
    
    //coach Details
    function getCoachDetails(){
        
        $query = "SELECT * FROM admin WHERE username = '".$this->username."' AND password = '".$this->password."' ";
        $result = mysqli_query($this->conn,$query);
        
        if($result){
            // select all query
            $query2 = "SELECT * FROM coachProfile ORDER BY coachProfile.firstname ASC";
                
            $result2 = mysqli_query($this->conn,$query2);
            $row2 = mysqli_fetch_all($result2,MYSQLI_ASSOC);
            return $row2;
        }else{
            $row = mysqli_fetch_all($result,MYSQLI_ASSOC);
            return $row;
        }
    }
    
    //coach program list old
    /*
    function getCoachProgramList(){
        
        $query = "SELECT * FROM admin WHERE username = '".$this->username."' AND password = '".$this->password."' ";
        $result = mysqli_query($this->conn,$query);
        
        if($result){
            // select all query
            $query2 = "SELECT programName, status FROM programList WHERE coachList LIKE '%".$this->coachID."%';";
                
            $result2 = mysqli_query($this->conn,$query2);
            $row2 = mysqli_fetch_all($result2,MYSQLI_ASSOC);
            return $row2;
        }else{
            return false;
        }
    }*/
    
    //coach program list updated
    function getCoachProgramList(){
        // Prepare the first query
        $query = "SELECT * FROM admin WHERE username = ? AND password = ?";
        $stmt = $this->conn->prepare($query);
        $stmt->bind_param("ss", $this->username, $this->password);
        $stmt->execute();
        $result = $stmt->get_result();
        
        if($result && $result->num_rows > 0){
            // Prepare the second query using JSON_CONTAINS
            $query2 = "SELECT programName, status FROM programList WHERE JSON_CONTAINS(coachList, ?)";
            $stmt2 = $this->conn->prepare($query2);
            $coachID_param = '"'.$this->coachID.'"'; // JSON_CONTAINS expects a string argument in JSON format
            $stmt2->bind_param("s", $coachID_param);
            $stmt2->execute();
            $result2 = $stmt2->get_result();
            $row2 = $result2->fetch_all(MYSQLI_ASSOC);
            return $row2;
        }else{
            return false;
        }
    }


    
    //coach Details
    function getCoachSessions(){
        
        $query = "SELECT * FROM admin WHERE username = '".$this->username."' AND password = '".$this->password."' ";
        $result = mysqli_query($this->conn,$query);
        
        if($result){
            // select all query
            $query2 = "SELECT clientRequests.*, coachProfile.firstname, coachProfile.lastname, clientProfile.schoolName 
          FROM clientRequests 
          JOIN coachProfile 
            ON clientRequests.coachID = coachProfile.coachID 
          JOIN clientProfile 
            ON clientRequests.username = clientProfile.username 
          WHERE clientRequests.coachID = '".$this->coachID."'";
                
            $result2 = mysqli_query($this->conn,$query2);
            $row2 = mysqli_fetch_all($result2,MYSQLI_ASSOC);
            return $row2;
        }else{
            return false;
        }
    }
    
    //program List
    function getProgramList(){
        $query = "SELECT * FROM programList";
        $result = mysqli_query($this->conn,$query);
        $row = mysqli_fetch_all($result,MYSQLI_ASSOC);
        return $row;
    }
    
    //program List
    function getCourseList(){
        
        $query = "SELECT {$this->tableName}.*, coachProfile.firstname, coachProfile.lastname FROM {$this->tableName} LEFT JOIN coachProfile ON {$this->tableName}.coachName = coachProfile.username";
        $stmt = $this->conn->prepare($query);
        $stmt->execute();
        $result = $stmt->get_result();
        
        $row = $result->fetch_all(MYSQLI_ASSOC);

        return $row;
    }
    
    //admin Client Enrolled List
    function getClientEnrolledSessions(){
    
        $final_result = [];
        
        $query = "SELECT clientRequests.*, coachProfile.firstname, coachProfile.lastname FROM clientRequests JOIN coachProfile ON clientRequests.coachID = coachProfile.coachID WHERE clientRequests.username = '".$this->username."'";
        
        $result = $this->conn->query($query);
        if ($result->num_rows > 0){
            while ($row = $result->fetch_assoc()) {
                $final_result[] = $row;
            }
        } else {
            echo "0 results";
            return false;
        }
        
        if(!empty($final_result)){
            return $final_result;
        }else{
            return false;
        }
    }
    
    function updateInNo(){
        
        foreach ($this->requestedCourseList as $course) {
            //echo $course->reqID . "\n";
            // Prepare and bind
            $stmt = $this->conn->prepare("UPDATE clientRequests SET invID = ?, invNo = ? WHERE reqID = ?");
            $stmt->bind_param("sss", $this->invID, $this->invNo, $course->reqID);
            if ($stmt->execute()) {
                //echo "Record updated successfully";
            } else {
                echo "Error updating record: " . $stmt->error;
            }
        }
        
        return true;
    }
    
    function getOverallReport(){
        $start_date = $this->reportStart;
        $end_date = $this->reportEnd;
        $username = $this->username;
        $password = $this->password;
    
        // Check connection
        if ($this->conn->connect_error) {
            die("Connection failed: " . $this->conn->connect_error);
        }

        // Prepare and execute the initial SQL statement
        $query = "SELECT * FROM admin WHERE username = ? AND password = ?";
        $stmt = $this->conn->prepare($query);
        $stmt->bind_param('ss', $username, $password);
        $stmt->execute();
        $result = $stmt->get_result();
    
        if ($result->num_rows > 0) {
            // Close the first statement before preparing the next one
            $stmt->close();
        
            // Prepare the SQL statement for clientRequests
            $sql = "SELECT * FROM clientRequests WHERE startTime BETWEEN ? AND ? AND endTime BETWEEN ? AND ? AND status = 'ACCEPTED'";
            $stmt = $this->conn->prepare($sql);
            $stmt->bind_param('ssss', $start_date, $end_date, $start_date, $end_date);
        
            // Execute statement
            $stmt->execute();
        
            // Get the result
            $result = $stmt->get_result();
        
            if ($result->num_rows > 0) {
                $rows = array();
                while ($row = $result->fetch_assoc()) {
                    $rows[] = $row;
                }
                return $rows;
            } else {
                echo "Empty Data";
                return false;
            }
        } else {
            echo "Fail Admin";
            return false;
        }
    
        // Close statement and connection
        $stmt->close();
        $this->conn->close();
    }

}
//Changes from Eclipse
?>