<?php

class Users{
    // database connection
    private $conn;
  
    // object properties
    public $username;
    public $password;
    public $accDetails;
    public $coachID;
    public $blackoutDates;
    
    //bukku
    public $bukkuID;
    public $bukkuContactID;
    
    //account activation
    public $account;
    
    //schedule update
    public $day, $data;
    
    // constructor with $db as database connection
    public function __construct($db){
        $this->conn = $db;
    }
    
    // user login
    function login(){
        
        // select all query
        $query = "SELECT accType FROM idList WHERE username= '".$this->username."' ";

        if ($result = mysqli_query($this->conn,$query))
        {
            $row=mysqli_fetch_row($result);
            if(!empty($row[0])){
                switch($row[0]){
                    case 'coach':
                        $subQuery = "SELECT * FROM coachProfile WHERE username= '".$this->username."' AND password= '".$this->password."'";
                        $firstResult = mysqli_query($this->conn, $subQuery);
                        $firstData = mysqli_fetch_assoc($firstResult); // Fetch a single row as an associative array
                        
                        if (!$firstData) {
                            return ["error" => "Invalid username or password"];
                        }
                        
                        $coachID = $firstData['coachID']; // Now you can access coachID
                        
                        $subQuery2 = "SELECT docType, JSON_ARRAYAGG(JSON_ARRAY(docName, doc)) AS docData FROM coachDocs WHERE coachID = '".$coachID."' GROUP BY docType";
                        $subQuery2Result = mysqli_query($this->conn, $subQuery2);
                        $data = mysqli_fetch_all($subQuery2Result, MYSQLI_ASSOC);
                        
                        // Add second query result at the end of first query result
                        $firstData["documents"] = $data;
                        
                        return $firstData;
                        break;
                    case 'customer':
                        $subQuery = "SELECT * FROM customerProfile WHERE username= '".$this->username."' AND password= '".$this->password."' ";
                        $finalResult = mysqli_query($this->conn,$subQuery);
                        $data = mysqli_fetch_assoc($finalResult);
                        return $data;
                        break;
                    case 'client':
                        $subQuery = "SELECT * FROM clientProfile WHERE username= '".$this->username."' AND password= '".$this->password."' ";
                        $finalResult = mysqli_query($this->conn,$subQuery);
                        $data = mysqli_fetch_assoc($finalResult);
                        return $data;
                        break;
                    case 'admin':
                        $subQuery = "SELECT * FROM admin WHERE username= '".$this->username."' AND password= '".$this->password."' ";
                        $finalResult = mysqli_query($this->conn,$subQuery);
                        $data = mysqli_fetch_assoc($finalResult);
                        return $data;
                        break;
                    default:
                        echo 'error';
                        return 'error';
                        break;
                }
            }else{
                return 'error';
            }
        }
    }
    
    //check coach availability
    function checkCoachAvailability(){
        
        // select all query
        $query = "SELECT session.*, coachProfile.blackoutDates FROM session JOIN coachProfile ON session.coachID = coachProfile.coachID WHERE session.coachID= '".$this->username."' ";
        $result = mysqli_query($this->conn,$query);
        return $result;
    }
    
    //check username
    function checkUsername(){
        
        // select all query
        $query = "SELECT * FROM idList WHERE username= '".$this->username."'";
        $result = mysqli_query($this->conn,$query);
        return $result;
    }
    
    function checkProgramName($programName, $username){
        
        $checkProgramName = "SELECT programName FROM programList WHERE programName = '".$programName."' ";
        $resultCheckProgramName = mysqli_query($this->conn, $checkProgramName);
        $row = mysqli_fetch_assoc($resultCheckProgramName);
        
        $currentDateTime= date('Y-m-d H:i:s');
        
        if(!empty($row['programName'])){
            
            $sql = "UPDATE programList SET coachList = JSON_ARRAY_APPEND(coachList, '$', '$username') WHERE programName = '".$programName."'";
            if($resultUpdateCoachID = mysqli_query($this->conn, $sql)){
                return true;
            }else{
                echo "Error occured: ". $this->conn->error;
                return false;
            }

        }else{
            
            $json = json_encode($username);
            
            $sql = "INSERT INTO programList (programName, coachList, status) VALUES ('$programName','[$json]', 'PENDING')";
            
            if($resultProgramList = mysqli_query($this->conn, $sql)){
                return true;
            }else{
                echo "Error occured: ". $this->conn->error;
                return false;
            }
        }
        
    }
    
    function createAcc() {
        $details = json_decode($this->accDetails, true);

        $username = $details["username"];
        $accType = $details["accType"];

        try {
            $stmt = $this->conn->prepare("INSERT INTO idList (username, accType) VALUES (?, ?)");
            
            if (!$stmt) {
                throw new Exception($this->conn->error);
            }
            
            $stmt->bind_param("ss", $username, $accType);

            if ($stmt->execute()) {
                switch ($accType) {
                    case 'coach':
                        $password = $details["password"];
                        $firstname = $details["firstname"];
                        $lastname = $details["lastname"];
                        $bio = $details["bio"];
                        $skills = $details["skills"];
                        $email = $details["email"];
                        $contactNo = $details["contactNo"];
                        $emergencyName = $details["emergencyName"];
                        $emergencyNo = $details["emergencyNo"];
                        $forSchools = $details["forSchools"];
                        $entityType = $details["entityType"];

                        $bukkuID = $this->bukkuID;
                        $bukkuContactID = $this->bukkuContactID;

                        $subStmt = $this->conn->prepare("INSERT INTO coachProfile 
                            SET username=?, password=?, firstname=?, lastname=?, bio=?, skills=?, email=?, contactNo=?, 
                            emergencyName=?, emergencyNo=?, forSchools=?, entityType=?, bukkuID=?, bukkuContactID=?");
                        
                        if (!$subStmt) {
                            throw new Exception($this->conn->error);
                        }
                    
                        $subStmt->bind_param("sssssssssssiis", 
                            $username, $password, $firstname, $lastname, $bio, 
                            $skills, $email, $contactNo, $emergencyName, $emergencyNo, 
                            $forSchools, $entityType, $bukkuID, $bukkuContactID);
                        break;

                    case 'customer':
                        $password = $details["password"];
                        $firstname = $details["firstname"];
                        $lastname = $details["lastname"];
                        $age = $details["age"];
                        $gender = $details["gender"];
                        $area = $details["area"];
                        $email = $details["email"];
                        $occupation = $details["occupation"];
                        $contactNo = $details["contactNo"];
                        $emergencyName = $details["emergencyName"];
                        $emergencyNo = $details["emergencyNo"];
                        $entityType = $details["entityType"];

                        $bukkuID = $this->bukkuID;
                        $bukkuContactID = $this->bukkuContactID;

                        $subStmt = $this->conn->prepare("INSERT INTO customerProfile 
                            SET username=?, password=?, firstname=?, lastname=?, age=?, gender=?, area=?, email=?, occupation=?, 
                            contactNo=?, emergencyName=?, emergencyNo=?, entityType=?, bukkuID=?, bukkuContactID=?");
                        
                        if (!$subStmt) {
                            throw new Exception($this->conn->error);
                        }
                    
                        $subStmt->bind_param("ssssissssssssii", 
                            $username, $password, $firstname, $lastname, $age, 
                            $gender, $area, $email, $occupation, $contactNo, 
                            $emergencyName, $emergencyNo, $entityType, $bukkuID, $bukkuContactID);
                        break;

                    case 'client':
                        $password = $details["password"];
                        $schoolName = $details["schoolName"];
                        $schoolAddress = $details["schoolStreet"] ." ". $details["schoolPostcode"] .", ". $details["schoolCity"] .", ". $details["schoolState"];
                        $branch = $details["branch"];
                        $email = $details["email"];
                        $picName = $details["firstname"] . " " . $details["lastname"];
                        $picNo = $details["picNo"];
                        $studentCategory = $details["studentCategory"];
                        $entityType = $details["entityType"];
                        $bukkuID = $this->bukkuID;
                        $bukkuContactID = $this->bukkuContactID;

                        $subStmt = $this->conn->prepare("INSERT INTO clientProfile 
                            SET username=?, password=?, schoolName=?, schoolAddress=?, branch=?, email=?, picName=?, picNo=?, 
                            studentCategory=?, entityType=?, bukkuID=?, bukkuContactID=?");
                        
                        if (!$subStmt) {
                            throw new Exception($this->conn->error);
                        }
                    
                        $subStmt->bind_param("ssssssssssii", 
                            $username, $password, $schoolName, $schoolAddress, 
                            $branch, $email, $picName, $picNo, 
                            $studentCategory, $entityType, $bukkuID, $bukkuContactID);
                        break;
                }
                
                if ($subStmt->execute()) {
                    if ($subStmt->affected_rows > 0) {
                        $subStmt->close();
                        $stmt->close();
                        return true;
                    } else {
                        $subStmt->close();
                        $stmt->close();
                        echo "No changes were made.";
                        return false;
                    }
                } else {
                    throw new Exception($subStmt->error);
                }
            } else {
                throw new Exception($stmt->error);
            }
        } catch (Exception $e) {
            echo "Error: " . $e->getMessage();
            return false;
        }
    }

    //update User Account
    function updateAcc(){
        
        $details = json_decode($this->accDetails, true);
        
        $accType=$details["accType"];
        
        switch ($accType){
            case 'coach':
                
                $oldUsername=$details["oldUsername"];
                $username=$details["username"];
                $password=$details["password"];
                $firstname=$details["firstname"];
                $lastname=$details["lastname"];
                $bio=$details["bio"];
                $skills=$details["skills"];
                $email=$details["email"];
                $contactNo=$details["contactNo"];
                $emergencyName=$details["emergencyName"];
                $emergencyNo=$details["emergencyNo"];
                
                $query = "UPDATE coachProfile SET username='".$username."', password='".$password."', firstname='".$firstname."', lastname='".$lastname."', bio='".$bio."', skills='".$skills."', email='".$email."', contactNo='".$contactNo."', emergencyName='".$emergencyName."', emergencyNo='".$emergencyNo."'  WHERE username='".$oldUsername."' ";
                
                $query2 = "SELECT * FROM coachProfile WHERE username='".$username."'";

                break;
                
            case 'customer':
                
                $oldUsername=$details["oldUsername"];
                $username=$details["username"];
                $password=$details["password"];
                $firstname=$details["firstname"];
                $lastname=$details["lastname"];
                $age=$details["age"];
                $gender=$details["gender"];
                $area=$details["area"];
                $email=$details["email"];
                $occupation=$details["occupation"];
                $contactNo=$details["contactNo"];
                $emergencyName=$details["emergencyName"];
                $emergencyNo=$details["emergencyNo"];
                
                $query = "UPDATE customerProfile SET username='".$username."', password='".$password."', firstname='".$firstname."', lastname='".$lastname."', age='".$age."', gender='".$gender."', area='".$area."', email='".$email."', occupation='".$occupation."', contactNo='".$contactNo."', emergencyName='".$emergencyName."', emergencyNo='".$emergencyNo."' WHERE username='".$oldUsername."' ";
                
                $query2 = "SELECT * FROM customerProfile WHERE username='".$username."'";

                break;
            case 'client':
                
                $oldUsername=$details["oldUsername"];
                $username=$details["username"];
                $password=$details["password"];
                $schoolName=$details["schoolName"];
                $schoolAddress=$details["schoolAddress"];
                $email=$details["email"];
                $picName=$details["firstname"] . " " . $details["lastname"];
                $picNo=$details["picNo"];
                $studentCategory=$details["studentCategory"];
                
                $query = "UPDATE clientProfile SET username='".$username."', password='".$password."', schoolName='".$schoolName."', schoolAddress='".$schoolAddress."', email='".$email."', picName='".$picName."', picNo='".$picNo."', studentCategory='".$studentCategory."' WHERE username='".$oldUsername."' ";
                
                $query2 = "SELECT * FROM clientProfile WHERE username='".$username."'";

                break;
            default:
                // set response code - 405 bad request
                http_response_code(405);
                // tell the user
                echo json_encode(array("message" => "Invalid Method"));
                break;
        }
        
        
        if($result = mysqli_query($this->conn,$query)){
           
           $subQuery = "UPDATE idList SET username='".$username."' WHERE username='".$oldUsername."' AND accType='".$accType."'";
           
           if($result2 = mysqli_query($this->conn,$subQuery)){
               
               $result3 = mysqli_query($this->conn,$query2);
               $row = mysqli_fetch_all($result3,MYSQLI_ASSOC);
               return $row[0];
               
           }else{
               return null;
           }
           
        }else{
            return null;
        }
        
    }
    
    function coachUpdateBlackOutDates(){
        
        $details = json_decode($this->blackoutDates, true); 
        // Access the username 
        $username = $details['username']; 
        $associativeArray = []; 
        foreach ($details['blackoutDates'] as $key => $value) { $associativeArray[$key] = $value; } 
        // Retrieve the subject from the blackoutDates array 
        $subject = $details['blackoutDates']['subject']; 
        
        $query = "SELECT blackoutDates FROM coachProfile WHERE JSON_EXTRACT(blackoutDates, '$') AND username = '$username' ";
        
        $result = mysqli_query($this->conn, $query);
        $row = mysqli_fetch_all($result,MYSQLI_ASSOC);
        
        if(empty($row)){
            $correctedData = [$subject => $associativeArray];
            $correctedJsonString = json_encode($correctedData); 
            $query2 = "UPDATE coachProfile SET blackoutDates = '$correctedJsonString' WHERE username = '$username' ";
        }else{
            $correctedData = [$subject => $associativeArray];
            $correctedJsonString = json_encode($correctedData); 
            $query2 = "UPDATE coachProfile SET blackoutDates = JSON_ARRAY_APPEND(blackoutDates, '$', CAST('$correctedJsonString' AS JSON)) WHERE username = '$username'";
        }
        
        if($result2 = mysqli_query($this->conn,$query2)){
            
            $query3 = "SELECT blackoutDates FROM coachProfile WHERE username = '$username' ";
            
            $result3 = mysqli_query($this->conn, $query3);
            $row2 = mysqli_fetch_all($result3,MYSQLI_ASSOC);
            
            return $row2;
            
            }else{
            
            return 'Error';
            
            }
    }

    function accountActivation($emailSender) {
        
        $accType = $this->account->accType;
        $username = $this->account->username;
        $status = $this->account->status;
        $email = $this->account->email;
        
        switch ($accType) {
            case 'client':
                $tablename = 'clientProfile';
                break;
            case 'coach':
                $tablename = 'coachProfile';
                break;
            default:
                $tablename = 'customerProfile';
                break;
        }
    
        $stmt = $this->conn->prepare("UPDATE $tablename SET `status`=? WHERE `username`=?");
        $stmt2 = $this->conn->prepare("UPDATE idList SET `status`=? WHERE `username`=? AND `accType`=?");
    
        if ($stmt === false || $stmt2 === false) {
            return $this->conn->error; // Return error message if prepare fails
        }

        $stmt->bind_param("ss", $status, $username);
        $stmt2->bind_param("sss", $status, $username, $accType);
        
        if($status == 'ACTIVE'){
            try{
                $emailSender->sendEmail(
                    "support@superwelljuniors.com", // From email
                    "$email",      // To email
                    "Superwell Juniors Registration: Welcome to Superwell Juniors",     // Subject
                    "Your id (".$username.") is activated. You may start using our services via Superwell Easybook app !" // Body
                    );
            } catch (Exception $e){
                echo 'Error sending mail:'. $e->getMessage();
            }
        }else{
            try{
                $emailSender->sendEmail(
                    "support@superwelljuniors.com", // From email
                    "$email",      // To email
                    "Superwell Juniors Registration: Your application has rejected",     // Subject
                    "Good day, hereby we inform that your application has been rejected. You may contact our team for further queries" // Body
                    );
            } catch (Exception $e){
                echo 'Error sending mail:'. $e->getMessage();
            }
        }

        if ($stmt->execute() && $stmt2->execute()) {
            if ($stmt->affected_rows > 0 && $stmt2->affected_rows > 0) {
                return true;
            } else {
                return "No rows affected. Update may not have happened.";
            }
        } else {
            return $stmt->error; // Return error message if execute fails
        }
    }

    function updateCoachSchedule() {
        
        $stmt = $this->conn->prepare("UPDATE coachProfile SET schedule = JSON_SET(schedule, '$." . $this->day . "', JSON_ARRAY(?)) WHERE coachID = ?");
        
        if ($stmt === false) {
            return $this->conn->error; // Return error message if prepare fails
        }
        
        $stmt->bind_param("si", $this->data, $this->coachID);
        
        if ($stmt->execute()) {
            if ($stmt->affected_rows > 0) {
                return true; // Update was successful
            } else {
                return "No rows affected. Update may not have happened.";
            }
        } else {
            return $stmt->error; // Return error message if execute fails
        }
        
    }
    
    function createCoachSchedule() {
        
        $stmt = $this->conn->prepare("UPDATE coachProfile SET schedule = ? WHERE coachID = ?");
        $newSchedule = json_encode([
            'MON' => [$this->data->monData],
            'TUE' => [$this->data->tueData],
            'WED' => [$this->data->wedData],
            'THU' => [$this->data->thuData],
            'FRI' => [$this->data->friData],
            'SAT' => [$this->data->satData],
            'SUN' => [$this->data->sunData],
        ]);
            
        if ($stmt === false) {
            return $this->conn->error; // Return error message if prepare fails
        }
        
        $stmt->bind_param('si', $newSchedule, $this->coachID);
        
        if ($stmt->execute()) {
            if ($stmt->affected_rows > 0) {
                return true; // Update was successful
            } else {
                return "No rows affected. Update may not have happened.";
            }
        } else {
            return $stmt->error; // Return error message if execute fails
        }
        
    }
}
?>