<?php
class ClientRequests{
  
    // database connection
    private $conn;
  
    // object properties
    public $coachID;
    public $newSession;
    public $customerID;
  
    // constructor with $db as database connection
    public function __construct($db){
        $this->conn = $db;
    }
    
    //Coach Get Request Sessions
    function coachGetClientRequests(){
        $subQuery = "SELECT * FROM clientRequests WHERE coachID = '".$this->coachID."' ";
                $finalResult = mysqli_query($this->conn,$subQuery);
                return $finalResult;
    }
    
    //Client Get Request Sessions
    function clientGetRequestSessions(){
        $subQuery = "SELECT * FROM clientRequests WHERE username = '".$this->customerID."' ";
                $finalResult = mysqli_query($this->conn,$subQuery);
                return $finalResult;
    }
    
    //Client Request New Sessions
    function newClientRequest(){
        
        $data = $this->newSession->requestedCourseList;
        
        if (json_last_error() === JSON_ERROR_NONE) {
            
            foreach ($data as $course) {
                $reqID = $course->reqID;
                $productID = $course->productID;
                $coachID = $course->coachID;
                $username = $course->username;
                $programName = $course->programName;
                $startTime = $course->startTime;
                $endTime = $course->endTime;
                $finalDate = $course->finalDate;
                $sessionPlace = $course->sessionPlace;
                $recurrenceRule = $course->recurrenceRule;
                $recurrenceExceptionDates = $course->recurrenceExceptionDates;
                $status = $course->status;
                $type = $course->type;
                $sessionCost = $course->sessionCost;
                $sessionCostID = $course->sessionCostID;
                $sessionCount = $course->sessionCount;
                $sessionTotalCost = $course->sessionTotalCost;
                
                $checkQuery = "SELECT * FROM `clientRequests` WHERE reqID = '".$reqID."' AND coachID = '".$coachID."' AND validate = '1' ";
                $result = mysqli_query($this->conn,$checkQuery);
        
            if(!empty($result->num_rows > 0)){
                
                $row = $result->fetch_assoc();

                $oldFinalDate = new DateTime($row['finalDate']);
                $newFinalDate = new DateTime($finalDate);
                
                $oldEndDate = new DateTime($row['endTime']);
                $newEndlDate = new DateTime($endTime);
                
                $oldStartDate = new DateTime($row['startTime']);
                $newStartDate = new DateTime($startTime);
                
                if ($newStartDate == $oldStartDate && $newEndDate == $oldEndDate) {
                    
                    $response=array(
                        'status' => 0,
                        'status_message' =>"This requested session is already exist: REQID-> $reqID ");
                        return $response;

                } else {
                    
                    $tempQuery = "UPDATE `clientRequests` SET validate = '2' where reqID = '".$reqID."' ";
                    $tempResult = mysqli_query($this->conn,$tempQuery);

                    // insert
                    if(empty($recurrenceRule)){
                        $query2 = "INSERT INTO `clientRequests`(`reqID`,`productID`, `coachID`, `username`, `programName`, `startTime`, `endTime`, `finalDate`, `sessionPlace`, `status`, `type`, `sessionCost`, `sessionCostID`, `sessionCount`, `sessionTotalCost`) VALUES 
                        ('".$reqID."','".$productID."','".$coachID."','".$username."','".$programName."','".$startTime."','".$endTime."','".$finalDate."','".$sessionPlace."','".$status."','".$type."','".$sessionCost."','".$sessionCostID."','".$sessionCount."','".$sessionTotalCost."')";
                    }else{
                        if(empty($recurrenceExceptionDates)){
                            $query2 = "INSERT INTO `clientRequests`(`reqID`,`productID`, `coachID`, `username`, `programName`, `startTime`, `endTime`, `finalDate`, `sessionPlace`, `recurrenceRule`, `status`, `type`, `sessionCost`, `sessionCostID`, `sessionCount`, `sessionTotalCost`) VALUES 
                            ('".$reqID."','".$productID."','".$coachID."','".$username."','".$programName."','".$startTime."','".$endTime."','".$finalDate."','".$sessionPlace."','".$recurrenceRule."','".$status."','".$type."','".$sessionCost."','".$sessionCostID."','".$sessionCount."','".$sessionTotalCost."')";
                        }else{
                            $query2 = "INSERT INTO `clientRequests`(`reqID`,`productID`, `coachID`, `username`, `programName`, `startTime`, `endTime`, `finalDate`, `sessionPlace`, `recurrenceRule`, `recurrenceExceptionDates`, `status`, `type`, `sessionCost`, `sessionCostID`, `sessionCount`, `sessionTotalCost`) VALUES 
                            ('".$reqID."','".$productID."','".$coachID."','".$username."','".$programName."','".$startTime."','".$endTime."','".$finalDate."','".$sessionPlace."','".$recurrenceRule."','".$recurrenceExceptionDates."','".$status."','".$type."','".$sessionCost."','".$sessionCostID."','".$sessionCount."','".$sessionTotalCost."')";
                        }
                    }
                }
            
            }else{
                // insert
                if(empty($recurrenceRule)){
                    $query2 = "INSERT INTO `clientRequests`(`reqID`,`productID`, `coachID`, `username`, `programName`, `startTime`, `endTime`, `finalDate`, `sessionPlace`, `status`, `type`, `sessionCost`, `sessionCostID`, `sessionCount`, `sessionTotalCost`) VALUES 
                    ('".$reqID."','".$productID."','".$coachID."','".$username."','".$programName."','".$startTime."','".$endTime."','".$finalDate."','".$sessionPlace."','".$status."','".$type."','".$sessionCost."','".$sessionCostID."','".$sessionCount."','".$sessionTotalCost."')";
                }else{
                    if(empty($recurrenceExceptionDates)){
                        $query2 = "INSERT INTO `clientRequests`(`reqID`,`productID`, `coachID`, `username`, `programName`, `startTime`, `endTime`, `finalDate`, `sessionPlace`, `recurrenceRule`, `status`, `type`, `sessionCost`, `sessionCostID`, `sessionCount`, `sessionTotalCost`) VALUES 
                        ('".$reqID."','".$productID."','".$coachID."','".$username."','".$programName."','".$startTime."','".$endTime."','".$finalDate."','".$sessionPlace."','".$recurrenceRule."','".$status."','".$type."','".$sessionCost."','".$sessionCostID."','".$sessionCount."','".$sessionTotalCost."')";
                    }else{
                        $query2 = "INSERT INTO `clientRequests`(`reqID`,`productID`, `coachID`, `username`, `programName`, `startTime`, `endTime`, `finalDate`, `sessionPlace`, `recurrenceRule`, `recurrenceExceptionDates`, `status`, `type`, `sessionCost`, `sessionCostID`, `sessionCount`, `sessionTotalCost`) VALUES 
                        ('".$reqID."','".$productID."','".$coachID."','".$username."','".$programName."','".$startTime."','".$endTime."','".$finalDate."','".$sessionPlace."','".$recurrenceRule."','".$recurrenceExceptionDates."','".$status."','".$type."','".$sessionCost."','".$sessionCostID."','".$sessionCount."','".$sessionTotalCost."')";
                        }
                    }
            }
            
            if($result2 = mysqli_query($this->conn,$query2)){
                    $response=array(
                        'status' => 1,
                        'status_message' =>'Request Session Added Successfully.');
                }else{
                    $response=array(
                        'status' => 0,
                        'status_message' =>'Session Request Addition Failed.');
                }
            }
            
            return $response;
            
        } else {
            echo "Failed to decode JSON: " . json_last_error_msg();
            $response=array(
                        'status' => 0,
                        'status_message' =>'Session Request Addition Failed.');
                    return $response;
        }
        
    }
    
    //Coach Update Client Request Sessions
    function updateClientRequest() {
        $reqNo = $this->newSession->reqNo;
        $coachID = $this->newSession->coachID;
        $productID = $this->newSession->productID;
        $status = $this->newSession->status;
        $timeSlots = $this->newSession->timeSlotsList;
        
        // Update status query
        $query = "UPDATE clientRequests SET status = ? WHERE reqNo = ?";
        $stmt = $this->conn->prepare($query);
        
        if (!$stmt) {
            die("Prepare failed: " . $this->conn->error);
        }
        
        // Bind parameters
        $stmt->bind_param("ss", $status, $reqNo);
        
        if ($status == 'ACCEPTED') {
            if ($stmt->execute()) {
                $updateTimeSlots = $this->updateTimeSlots($coachID, $productID,$timeSlots);
                $moveIntoSessions = $this->moveClientRequestsToSessions($reqNo);
                
                if ($moveIntoSessions && $updateTimeSlots) {
                    $response = array(
                        'status' => 1,
                        'status_message' => 'REQUEST ACCEPTED'
                    );
                    return $response;
                } else {
                    $response = array(
                        'status' => 0,
                        'status_message' => 'Request acceptance failed'
                    );
                    return $response;
                }
            }
        } elseif ($status == 'DECLINED') {
            if ($stmt->execute()) {
                $response = array(
                    'status' => 1,
                    'status_message' => 'REQUEST DECLINED.'
                );
                return $response;
            } else {
                $response = array(
                    'status' => 0,
                    'status_message' => 'Request update failed'
                );
                return $response;
            }
        }
        
        // Close the statement
        $stmt->close();
    }

    function moveClientRequestsToSessions($reqNo){
        // SQL SELECT query
        $sql = "SELECT * FROM clientRequests WHERE reqNo = ?";
        $stmt = $this->conn->prepare($sql);
        
        if (!$stmt) {
            die("Prepare failed: " . $this->conn->error);
        }
        
        // Bind the parameter to the query
        $stmt->bind_param("s", $reqNo);
        $stmt->execute();
        $result = $stmt->get_result();
        
        // Fetch and process each row
        while ($subData = $result->fetch_assoc()) {
            $clientReqNo = $subData["reqNo"];
            $sessionName = $subData["reqID"];
            $coachID = $subData["coachID"];
            $username = $subData["username"];
            $sessionAttendee = json_encode($username);
            $sessionDescription = $subData["programName"];
            $sessionStart = $subData["startTime"];
            $sessionEnd = $subData["endTime"];
            $finalDate = $subData["finalDate"];
            $sessionMode = 'Offline';
            $sessionPlace = $subData["sessionPlace"];
            $sessionHeadCost = $subData['sessionCost'];
            $sessionCount = $subData['sessionCount'];
            $sessionTotalRevenue = $subData['sessionTotalCost'];
            $recurrenceRule = $subData["recurrenceRule"];
            $recurrenceExceptionDates = $subData["recurrenceExceptionDates"];
            $forSchools = 1;
            
            if (empty($recurrenceRule)) {
                $sql2 = "INSERT INTO session (clientReqNo, coachID, sessionName, sessionDescription, sessionMode, sessionPlace, sessionStart, sessionEnd, finalDate, sessionHeadCost, sessionCount, sessionTotalRevenue, sessionAttendee, forSchools) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
                $stmt2 = $this->conn->prepare($sql2);
                $stmt2->bind_param("issssssssdidsi", $clientReqNo, $coachID, $sessionName, $sessionDescription, $sessionMode, $sessionPlace, $sessionStart, $sessionEnd, $finalDate, $sessionHeadCost, $sessionCount, $sessionTotalRevenue, $sessionAttendee, $forSchools);
            } elseif (empty($recurrenceExceptionDates)) {
                $sql2 = "INSERT INTO session (clientReqNo, coachID, sessionName, sessionDescription, sessionMode, sessionPlace, sessionStart, sessionEnd, finalDate, sessionHeadCost, sessionCount, sessionTotalRevenue, sessionAttendee, recurrenceRule, forSchools) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
                $stmt2 = $this->conn->prepare($sql2);
                $stmt2->bind_param("issssssssdidssi", $clientReqNo, $coachID, $sessionName, $sessionDescription, $sessionMode, $sessionPlace, $sessionStart, $sessionEnd, $finalDate, $sessionHeadCost, $sessionCount, $sessionTotalRevenue, $sessionAttendee, $recurrenceRule, $forSchools);
            } else {
                $sql2 = "INSERT INTO session (clientReqNo, coachID, sessionName, sessionDescription, sessionMode, sessionPlace, sessionStart, sessionEnd, finalDate, sessionHeadCost, sessionCount, sessionTotalRevenue, sessionAttendee, recurrenceRule, recurrenceExceptionDates, forSchools) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
                $stmt2 = $this->conn->prepare($sql2);
                $stmt2->bind_param("issssssssdidsssi", $clientReqNo, $coachID, $sessionName, $sessionDescription, $sessionMode, $sessionPlace, $sessionStart, $sessionEnd, $finalDate, $sessionHeadCost, $sessionCount, $sessionTotalRevenue, $sessionAttendee, $recurrenceRule, $recurrenceExceptionDates, $forSchools);
            }
            
            if (!$stmt2->execute()) {
                die("Execute failed: " . $stmt2->error);
            }
            
            if ($stmt2->affected_rows > 0) {
                $checkSessionID = "SELECT sessionID FROM session WHERE sessionName = ?";
                $stmt3 = $this->conn->prepare($checkSessionID);
                $stmt3->bind_param("s", $sessionName);
                $stmt3->execute();
                $resultSessionID = $stmt3->get_result();
                $row = $resultSessionID->fetch_assoc();
                $sessionID = $row["sessionID"];
                
                $checkEnrolledSessionQuery = "SELECT enrolledSessions FROM clientProfile WHERE JSON_EXTRACT(enrolledSessions, '$') AND username = ?";
                $stmt4 = $this->conn->prepare($checkEnrolledSessionQuery);
                $stmt4->bind_param("s", $username);
                $stmt4->execute();
                $resultEnrolledSessionQuery = $stmt4->get_result();
                $rowEnrolledSessionQuery = $resultEnrolledSessionQuery->fetch_assoc();
                
                if (empty($rowEnrolledSessionQuery)) {
                    $updateEnrolledSessionQuery = "UPDATE clientProfile SET enrolledSessions = ? WHERE username = ?";
                    $stmt5 = $this->conn->prepare($updateEnrolledSessionQuery);
                    $stmt5->bind_param("ss", $sessionID, $username);
                } else {
                    $updateEnrolledSessionQuery = "UPDATE clientProfile SET enrolledSessions = JSON_ARRAY_APPEND(enrolledSessions, '$', ?) WHERE username = ?";
                    $stmt5 = $this->conn->prepare($updateEnrolledSessionQuery);
                    $stmt5->bind_param("ss", $sessionID, $username);
                }
                
                if (!$stmt5->execute()) {
                    die("Execute failed: " . $stmt5->error);
                }
                
                return true;
            } else {
                return false;
            }
        }
        
        // Close the statement and the connection
        $stmt->close();
    }

    function updateTimeSlots($coachID,$productID,$timeSlotsList){
        $allInserted  = true;
        foreach ($timeSlotsList as $timeSlot){
            $startSlot = $timeSlot->startSlotTime;
            $endSlot = $timeSlot->endSlotTime;
            
            $addTimeSlotQuery = "INSERT INTO `timeSlots`(`coachID`, `productID`, `slotStart`, `slotEnd`) VALUES (?,?,?,?)";
            $stmt = $this->conn->prepare($addTimeSlotQuery);
            $stmt->bind_param("iiss",$coachID, $productID, $startSlot, $endSlot);
            if($stmt->execute()){
                $allInserted = true;
            }else{
                $allInserted = false;
                die("Execute failed: " . $stmt->error);
            }
        }
        return $allInserted;
    }
}
?>