<?php
class Sessions{
  
    // database connection
    private $conn;
    
    // object properties
    public $username;
    public $accType;
    public $password;
    public $coachID;
    public $sessionID;
    public $newSession;
  
    // constructor with $db as database connection
    public function __construct($db){
        $this->conn = $db;
    }
    
    //Customer All Sessions
    function customerSessions(){
        $subQuery = "SELECT session.*, coachProfile.fullname FROM session JOIN coachProfile ON session.coachID = coachProfile.coachID";
                $finalResult = mysqli_query($this->conn,$subQuery);
                return $finalResult;
    }
    
    //Coach All Sessions
    function coachSessions(){
        $subQuery = "SELECT * FROM session WHERE coachID = '".$this->coachID."' ";
                $finalResult = mysqli_query($this->conn,$subQuery);
                return $finalResult;
    }
    
    //Customer Request New Sessions
    function requestSessions(){
        
        $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"];
    
        // select all query
        $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))
        {
            $response=array(
                'status' => 1,
                'status_message' =>'Request Session Added Successfully.');
        }else{
            $response=array(
                'status' => 0,
                'status_message' =>'Session Request Addition Failed.');
        }
        
        return $response;
    }
    
    //Coach Add New Sessions
    function createSession(){
        
        $data = json_decode($this->newSession, true);
        $coachID=$data["coachID"];
        $sessionName=$data["sessionName"];
        $sessionDescription=$data["sessionDescription"];
        $sessionCategory=$data["sessionCategory"];
        $programCategory=$data["programCategory"];
        $sessionType=$data["sessionType"];
        $sessionMode=$data["sessionMode"];
        $sessionPlace=$data["sessionPlace"];
        $sessionLink=$data["sessionLink"];
        $sessionStart=$data["sessionStart"];
        $sessionEnd=$data["sessionEnd"];
        $sessionHeadCost=$data["sessionHeadCost"];
        $sessionTotalRevenue=$data["sessionTotalRevenue"];
        $recurrenceRule=$data["recurrenceRule"];
        
        if(!empty($programCategory)){
            $forSchools = 1;
            $programStatus = 'UNDER REVIEW';
        }else{
            $forSchools = 0;
            $programStatus = '';
        }
        
        if(!empty($sessionPlace)){
            $query = "INSERT INTO `session`( `coachID`, `sessionName`, `sessionDescription`, `sessionCategory`, `programCategory`, `sessionType`, `sessionMode`, `sessionPlace`, `sessionStart`, `sessionEnd`, `sessionHeadCost`, `sessionTotalRevenue`, `recurrenceRule`, `forSchools`, `programStatus`) VALUES ('".$coachID."','".$sessionName."','".$sessionDescription."','".$sessionCategory."','".$programCategory."','".$sessionType."','".$sessionMode."','".$sessionPlace."','".$sessionStart."','".$sessionEnd."','".$sessionHeadCost."','".$sessionTotalRevenue."','".$recurrenceRule."','".$forSchools."','".$programStatus."')";
        }else if(!empty($sessionLink)){
            $query = "INSERT INTO `session`( `coachID`, `sessionName`, `sessionDescription`, `sessionCategory`, `programCategory`, `sessionType`, `sessionMode`, `sessionLink`, `sessionStart`, `sessionEnd`, `sessionHeadCost`, `sessionTotalRevenue`, `recurrenceRule`, `forSchools`, `programStatus`) VALUES ('".$coachID."','".$sessionName."','".$sessionDescription."','".$sessionCategory."','".$programCategory."','".$sessionType."','".$sessionMode."','".$sessionLink."','".$sessionStart."','".$sessionEnd."','".$sessionHeadCost."','".$sessionTotalRevenue."','".$recurrenceRule."','".$forSchools."','".$programStatus."')";
        }
        
        $result = mysqli_query($this->conn,$query);
        
        if($result){
            return true;
        }else{
            return false;
        }
    }
    
    //Customer & Client Enroll Session
    function addSessionEnrollment(){
        // select all query
        $query = "SELECT accType FROM idList WHERE username= '".$this->username."' ";
        
        $result = mysqli_query($this->conn,$query);
        $row=mysqli_fetch_row($result);
        
        $accTable;
        
        if(!empty($row[0])){
            
                switch($row[0]){
                    case 'customer':
                        $accTable = 'customerProfile';
                        break;
                    case 'client':
                        $accTable = 'clientProfile';
                        break;
                    default:
                        return 'error';
                        break;
                }
                
                $loginQuery = "SELECT * FROM $accTable WHERE username= '".$this->username."' AND password= '".$this->password."' ";
                
                if($resultLoginQuery = mysqli_query($this->conn,$loginQuery)){
                    
                    $checkEnrolledSessionQuery = "SELECT enrolledSessions FROM $accTable WHERE JSON_EXTRACT(enrolledSessions, '$') AND username = '".$this->username."' ";
					$resultEnrolledSessionQuery = mysqli_query($this->conn, $checkEnrolledSessionQuery);
					$rowEnrolledSessionQuery = mysqli_fetch_all($resultEnrolledSessionQuery,MYSQLI_ASSOC);
					
					if(empty($rowEnrolledSessionQuery)){
					    $updateEnrolledSessionQuery = "UPDATE $accTable SET enrolledSessions = '".$this->sessionID."' WHERE username = '".$this->username."'";
					}else{
					    $updateEnrolledSessionQuery = "UPDATE $accTable SET enrolledSessions = JSON_ARRAY_APPEND(enrolledSessions, '$' ,'$this->sessionID') WHERE username = '".$this->username."' ";
					}
					
					$resultEnrolledSessionQuery = mysqli_query($this->conn,$updateEnrolledSessionQuery);
					
					
					$checkSessionAttendeeQuery = "SELECT sessionAttendee FROM session WHERE JSON_EXTRACT(sessionAttendee, '$') AND sessionID = '".$this->sessionID."' ";
					$resultSessionAttendeeQuery = mysqli_query($this->conn, $checkSessionAttendeeQuery);
					$rowSessionAttendeeQuery = mysqli_fetch_all($resultSessionAttendeeQuery,MYSQLI_ASSOC);
					
					if(empty($rowSessionAttendeeQuery)){
					    $updateSessionAttendeeQuery = "UPDATE session SET sessionAttendee = '".json_encode($this->username)."' WHERE sessionID = '".$this->sessionID."'";
					}else{
					    $updateSessionAttendeeQuery = "UPDATE session SET sessionAttendee = JSON_ARRAY_APPEND(sessionAttendee, '$' ,'$this->username') WHERE sessionID = '".$this->sessionID."' ";
					}
					
				    $resultSessionAttendeeQuery = mysqli_query($this->conn,$updateSessionAttendeeQuery);
					
					$finalQuery = "SELECT enrolledSessions FROM $accTable WHERE username = '".$this->username."' ";
					$finalResult = mysqli_query($this->conn, $finalQuery);
					$finalRow = mysqli_fetch_all($finalResult,MYSQLI_ASSOC);
						
					if($resultEnrolledSessionQuery && $resultSessionAttendeeQuery){
						return json_encode($finalRow);
					}else{
					    return json_encode($finalRow);
					}

                }else{
                    return 'CHECK JSON QUERY FAILED';
                }
                
            }else{
                return 'error';
            }
    }
    
    //Customer Get Enrolled Session
    function getEnrolledSession(){
        // select all query
        $query = "SELECT * FROM session WHERE JSON_CONTAINS(sessionAttendee, '\"$this->username\"', '$')";

        if ($result = mysqli_query($this->conn,$query))
        {
            $row = mysqli_fetch_all($result,MYSQLI_ASSOC);
            return $row;
            // Free the result set
            mysqli_free_result($result);
            
        }
    }
    
    function updateExcludedDates($excludedDateData) {
        $reqNo = $excludedDateData->reqNo;
        $newSessionCount = $excludedDateData->newSessionCount;
        $newSessionTotalCost = $excludedDateData->newSessionTotalCost;
        $recurrenceExceptionDates = $excludedDateData->recurrenceExceptionDates;
    
        if ($this->conn->connect_error) {
            die("Connection failed: " . $this->conn->connect_error);
        }
    
        $stmt = $this->conn->prepare("UPDATE clientRequests SET recurrenceExceptionDates = ?, sessionCount = ? , sessionTotalCost = ? WHERE reqNo = ?");
        if ($stmt === false) {
            die("Error preparing statement for clientRequests: " . $this->conn->error);
        }
        $stmt->bind_param("sidi", $recurrenceExceptionDates, $newSessionCount, $newSessionTotalCost, $reqNo);

        $stmt2 = $this->conn->prepare("UPDATE session SET recurrenceExceptionDates = ?, sessionCount = ?, sessionTotalRevenue = ? WHERE clientReqNo = ?");
        if ($stmt2 === false) {
            die("Error preparing statement for session: " . $this->conn->error);
        }
        $stmt2->bind_param("sidi", $recurrenceExceptionDates, $newSessionCount, $newSessionTotalCost, $reqNo);

        $execute1 = $stmt->execute();
        $execute2 = $stmt2->execute();
    
        if ($execute1 === false) {
            $stmt->close();
            $stmt2->close();
            $this->conn->close();
            die("Error executing statement for clientRequests: " . $stmt->error);
        }
    
        if ($execute2 === false) {
            $stmt->close();
            $stmt2->close();
            $this->conn->close();
            die("Error executing statement for session: " . $stmt2->error);
        }

        $stmt->close();
        $stmt2->close();
        $this->conn->close();
    
        return true;
    }


}
?>