<?php
class RequestSessions{
  
    // database connection
    private $conn;
  
    // object properties
    public $coachID;
    public $newSession;
    public $customerID;
    
    public $productID;
    public $dateTimes;
  
    // constructor with $db as database connection
    public function __construct($db){
        $this->conn = $db;
    }
    
    //Coach Get Request Sessions
    function coachGetRequestSessions(){
        $subQuery = "SELECT requestSession.*, session.* FROM requestSession JOIN session ON requestSession.sessionID = session.sessionID WHERE session.coachID = '".$this->coachID."' ";
                $finalResult = mysqli_query($this->conn,$subQuery);
                return $finalResult;
    }
    
    //Customer Get Request Sessions
    function customerGetRequestSessions(){
        $subQuery = "SELECT requestSession.*, session.* FROM requestSession JOIN session ON requestSession.sessionID = session.sessionID WHERE requestSession.username = '".$this->customerID."' ";
                $finalResult = mysqli_query($this->conn,$subQuery);
                return $finalResult;
    }
    
    //Customer Request New Sessions
    function createRequestSessions(){
        
        $data = json_decode($this->newSession, true);
        $reqID=$data["reqID"];
        $coachID=$data["coachID"];
        $username=$data["username"];
        $sessionID=$data["sessionID"];
        $newStartTime=$data["newStartTime"];
        $newEndTime=$data["newEndTime"];
        $status=$data["status"];
    
        // insert
        $query = "INSERT INTO requestSession SET reqID='".$reqID."', coachID='".$coachID."', username='".$username."', sessionID='".$sessionID."', newStartTime='".$newStartTime."', newEndTime='".$newEndTime."', status='".$status."' ";

        if($result = mysqli_query($this->conn,$query)){
            return true;
        }else{
            return false;
        }
    }
    
    //Coach Update Request Sessions
    function updateRequestSessions(){
        
        $data = json_decode($this->newSession, true);
        $reqID=$data["reqID"];
        $status=$data["status"];
        
        // update status query
        $query = "UPDATE requestSession SET status='".$status."' WHERE reqID ='".$reqID."' ";
        
        if($status=='ACCEPTED'){
            
            if($result = mysqli_query($this->conn,$query)){
                
                //get Session Details
                $subQuery1 = "SELECT requestSession.*, session.* FROM requestSession JOIN session ON requestSession.sessionID = session.sessionID WHERE requestSession.reqID = '".$reqID."' ";
                $subResult = mysqli_query($this->conn,$subQuery1);
                
                $subData = $subResult->fetch_assoc();

                $coachID=$subData["coachID"];
                $sessionName=$subData["sessionName"];
                $sessionDescription=$subData["sessionDescription"];
                $sessionCategory=$subData["sessionCategory"];
                $sessionType=$subData["sessionType"];
                $sessionMode=$subData["sessionMode"];
                $sessionPlace=$subData["sessionPlace"];
                $sessionLink=$subData["sessionLink"];
                $sessionStart=$subData["newStartTime"];
                $sessionEnd=$subData["newEndTime"];
                $sessionHeadCost=$subData["sessionHeadCost"];
                $sessionTotalRevenue=$subData["sessionTotalRevenue"];
                $sessionAttendee=json_encode($subData["username"]);
                $recurrenceRule=$subData["recurrenceRule"];
                
                if(!empty($sessionPlace)){
                    $subQuery2 = "INSERT INTO session SET coachID='".$coachID."', sessionName='".$sessionName."', sessionDescription='".$sessionDescription."', sessionCategory='".$sessionCategory."', sessionType='".$sessionType."', sessionMode='".$sessionMode."', sessionPlace='".$sessionPlace."', sessionStart='".$sessionStart."', sessionEnd='".$sessionEnd."', sessionHeadCost='".$sessionHeadCost."', sessionTotalRevenue='".$sessionTotalRevenue."', sessionAttendee='".$sessionAttendee."', recurrenceRule='".$recurrenceRule."'";
                }else if(!empty($sessionLink)){
                    $subQuery2 = "INSERT INTO session SET coachID='".$coachID."', sessionName='".$sessionName."', sessionDescription='".$sessionDescription."', sessionCategory='".$sessionCategory."', sessionType='".$sessionType."', sessionMode='".$sessionMode."', sessionLink='".$sessionLink."', sessionStart='".$sessionStart."', sessionEnd='".$sessionEnd."', sessionHeadCost='".$sessionHeadCost."', sessionTotalRevenue='".$sessionTotalRevenue."', sessionAttendee='".$sessionAttendee."', recurrenceRule='".$recurrenceRule."' ";
                }
                
                if($subResult2 = mysqli_query($this->conn,$subQuery2)){
                    return true;
                }else{
                    if(mysqli_errno($this->conn) == 1062)
                       return "This requested session is already exist. Request ID: '".$subData["reqID"]."'; Status: '".$subData["status"]."' ";
                }
            }else{
                return false;
            }
            
        }else if($status=='DECLINED'){
            if($result = mysqli_query($this->conn,$query)){
                return true;
            }else{
                 return false;
            }
        }
    }
    
    //check coach availability
    function checkCoachAvailability() {
        
        $rows = [];
        
        foreach ($this->dateTimes as $dateTime) {
            
            $sql = "SELECT slotStart, slotEnd FROM timeSlots WHERE coachID = ? AND DATE(slotStart) >= ? AND DATE(slotEnd) <= ?";
            
            $stmt = $this->conn->prepare($sql);
            
            if (!$stmt) {
                die("Query preparation failed: " . $this->conn->error);
            }
            
            $stmt->bind_param("sss", $this->coachID, $dateTime->startTime, $dateTime->endTime);
            $stmt->execute();
            $result = $stmt->get_result();
            
            if ($result->num_rows > 0) {
                while ($row = $result->fetch_assoc()) {
                    $rows[] = $row;
                }
            }
        }
        
        // Close connection
        $stmt->close();
        
        return $rows;
    }
}
?>