<?php
class ProgramList{
    
    public $username;
    public $password;
    
    public $programName;
    public $coachName;
    
    public $programData;
    public $bukkuGroupID;
    
    //Coure Update ID
    public $sku;
    public $bukkuID;
    public $priceID;
    public $midPriceID;
    public $highPriceID;
    public $trialPriceID;

    // database connection
    private $conn;
  
    // constructor with $db as database connection
    public function __construct($db){
        $this->conn = $db;
    }
    
    //program all program List (Coach Registration)
    function getProgramList(){
            $query = "SELECT programName, coachList FROM programList WHERE status = 'ACTIVE' ";
            $result = mysqli_query($this->conn,$query);
            $row = mysqli_fetch_all($result,MYSQLI_ASSOC);
            return $row;
    }
    
    //program all program List (Coach Registration)
    function getClientList(){
            $query = "SELECT username, schoolName FROM clientProfile";
            $result = mysqli_query($this->conn,$query);
            $row = mysqli_fetch_all($result,MYSQLI_ASSOC);
            return $row;
    }
    
    //coach List
    function getCoachList(){
        
        $sql = "SELECT coachList FROM programList WHERE programName = ?";
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param("s", $this->programName);
        $stmt->execute();
        $result = $stmt->get_result();
        
        $programName = str_replace(' ', '', $this->programName);
        $programName = strtolower($programName);
        
        if ($result->num_rows > 0) {
            $coachDetails = [];
            
            while ($row = $result->fetch_assoc()) {
                $coachList = $row['coachList'];
                // Decode the JSON data
                $coachArray = json_decode($coachList, true);
                // Process each coach in the array
                foreach ($coachArray as $coach) {
                    //Execute another query for each coach
                    $coachName = $coach;
                    $sql2 = "SELECT * FROM coachProfile t1 JOIN $programName t2 ON t1.username = t2.coachName WHERE t2.status ='ACTIVE' AND t2.coachName = ? ";
                    $stmt2 = $this->conn->prepare($sql2);
                    
                    // Check for preparation errors
                    if ($stmt2 === false) {
                        die('Prepare Error: ' . $mysqli->error);
                    }
                    
                    // Bind the parameter and execute the statement
                    $stmt2->bind_param('s', $coachName);
                    $stmt2->execute();
                    
                    // Fetch the results
                    
                    $result2 = $stmt2->get_result();
                    $row = $result2->fetch_assoc();
                    
                    // Check if results are empty
                    if (!empty($row)) {
                        $coachDetails[] = $row;
                    }
                }
            }
            
            $stmt2->close();
            $this->conn->close();
            
            
            if(!empty($coachDetails)){
                return $coachDetails;
            }else{
                return;
            }
            
        } else {
            $stmt->close();
            $this->conn->close();
            echo "No data found";
            return;
        }
    }

    //program List
    function getCourseList(){
        
        $sql = "SELECT coachList FROM programList WHERE programName = ?";
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param("s", $this->programName);
        $stmt->execute();
        $result = $stmt->get_result();
        
        $programName = str_replace(' ', '', $this->programName);
        $programName = strtolower($programName);
        
        if ($result->num_rows > 0) {
            $coachDetails = [];
            
            while ($row = $result->fetch_assoc()) {
                $coachList = $row['coachList'];
                // Decode the JSON data
                $coachArray = json_decode($coachList, true);
                // Process each coach in the array
                foreach ($coachArray as $coach) {
                    //Execute another query for each coach
                    $coachName = $coach;
                    $sql2 = "SELECT coachID, username, firstname, lastname, bio, skills, profileImg, schedule, blackoutDates, courseid, courseName, courseDesc, masterLevel, bookedSlots, price, priceID, trialPrice, trialPriceID, studentCategory, specificSchool FROM coachProfile t1 JOIN $programName t2 ON t1.username = t2.coachName WHERE t2.status ='ACTIVE' AND t2.coachName = ? ";
                    $stmt2 = $this->conn->prepare($sql2);
                    
                    // Check for preparation errors
                    if ($stmt2 === false) {
                        die('Prepare Error STMT 2: ' . $stmt2->error);
                    }
                    
                    // Bind the parameter and execute the statement
                    $stmt2->bind_param('s', $coachName);
                    $stmt2->execute();
                    
                    // Fetch the results
                    
                    $result2 = $stmt2->get_result();
                    while($row2 = $result2->fetch_assoc()){
                        // Check if results are empty
                    if (!empty($row2)) {
                        $coachDetails[] = $row2;
                    }
                    }
                }
            }
            
            $stmt2->close();
            $this->conn->close();
            
            
            if(!empty($coachDetails)){
                return $coachDetails;
            }else{
                return;
            }
            
        } else {
            $stmt->close();
            $this->conn->close();
            echo "No data found";
            return;
        }
    }
    
    //coach Get Program List
    function coachGetProgramList(){
        $query = "SELECT bukkuGroupID, tableName, programCategory, programName, status FROM programList WHERE JSON_CONTAINS(coachList, ?)";
        $stmt = $this->conn->prepare($query);
        $value = json_encode($this->coachName); // JSON string format
        $stmt->bind_param("s", $value);
        $stmt->execute();
        $result = $stmt->get_result();
        
        $row = mysqli_fetch_all($result,MYSQLI_ASSOC);
        return $row;
    }
    
    //coach Get Course List
    function coachGetCourseList(){
        
        // Prepare and execute the SQL query
        $sql = "SELECT programName FROM programList WHERE JSON_CONTAINS(coachList, ?)";
        $stmt = $this->conn->prepare($sql);
        $value = json_encode($this->coachName); // JSON string format
        $stmt->bind_param("s", $value);
        $stmt->execute();
        $result = $stmt->get_result();
        
        $programDetails = [];
        
        if ($result->num_rows > 0) {
            while ($row = $result->fetch_assoc()) {
                $programNames = $row['programName'];
                    $programName = str_replace(' ', '', $programNames);
                    $programName = strtolower($programName);
                    $sql2 = "SELECT * FROM $programName WHERE coachName = ? ";
                    $stmt2 = $this->conn->prepare($sql2);
                    
                    // Check for preparation errors
                    if ($stmt2 === false) {
                        die('Prepare Error: ' . $mysqli->error);
                    }
                    
                    // Bind the parameter and execute the statement
                    $stmt2->bind_param('s', $this->coachName);
                    $stmt2->execute();
                    
                    // Fetch the results
                    
                    $result2 = $stmt2->get_result();
                    
                    if($result2->num_rows>0){
                        $innerRows = [];
                        while($row2 = $result2->fetch_assoc()){
                            $row2['programName'] = $row['programName'];
                            $programDetails[] = $row2;
                        }
                    }
            }
            $stmt->close();
            $stmt2->close();
            $this->conn->close();
        } else {
            $stmt->close();
            $this->conn->close();
            return;
        }
        
        if(!empty($programDetails)){
            return $programDetails;
        }else{
            return;
        }
    }
    
    //coach Update Course Cost
    function coachUpdateCourseList(){
        
        $programName = $this->programData->programName;
        $programName = str_replace(' ', '', $programName);
        $programName = strtolower($programName);
        $coachName = $this->programData->coachName;
        $courseID = $this->programData->courseID;
        $newPrice = $this->programData->newPrice;
        $newTrialPrice = $this->programData->newTrialPrice;
        
        // Prepare the SQL statement with placeholders
        $sql = "SELECT status FROM $programName WHERE id = ? ";
        
        // Prepare the statement
        $stmt = $this->conn->prepare($sql);
        if ($stmt === false) {
            die("Prepare failed: " . $this->conn->error);
        }
        
        // Bind parameters
        $stmt->bind_param("s",$courseID);
            
            // Execute the statement
            if ($stmt->execute()) {
                
                $result = $stmt->get_result();
                $row = $result->fetch_assoc();
                
                switch($row['status']){
                        case 'ACTIVE':
                            
                            // Prepare the SQL statement with placeholders
                            $sql2 = "SELECT courseCreation FROM $programName WHERE id = ? ";
                            // Prepare the statement
                            $stmt2 = $this->conn->prepare($sql2);
                            if ($stmt2 === false) {
                                die("Prepare failed: " . $this->conn->error);
                            }
                            
                            // Bind parameters
                            $stmt2->bind_param("s",$courseID);
                            // Execute the statement
                            if ($stmt2->execute()) {
                                
                                $result2 = $stmt2->get_result();
                                $row2 = $result2->fetch_assoc();
                                
                                $courseCreationString = $row2['courseCreation'];
                                
                                $courseCreation = new DateTime($courseCreationString);
                                $currentDateTime= new DateTime();

                                // Calculate the difference
                                $interval = $courseCreation->diff($currentDateTime);
                                // Convert the difference to hours
                                $hoursDifference = ($interval->days * 24) + $interval->h + ($interval->i / 60) + ($interval->s / 3600);
                                
                                if ($hoursDifference < 48) {
                                    return "Error1";
                                } else {
                                    // Prepare the SQL statement with placeholders
                                    $sql3 = "UPDATE $programName SET price=?, trialPrice=?, courseCreation =? WHERE id = ? ";
                                    // Prepare the statement
                                    $stmt3 = $this->conn->prepare($sql3);
                                    if ($stmt3 === false) {
                                        die("Prepare failed: " . $this->conn->error);
                                    }
                                    
                                    $currentDateTimeString = $currentDateTime->format('Y-m-d H:i:s');
                                    
                                    // Bind parameters
                                    $stmt3->bind_param("ssss",$newPrice,$newTrialPrice,$currentDateTimeString,$courseID);
                                    // Execute the statement
                                    if ($stmt3->execute()) {
                                        return 'Success';
                                    } else {
                                        return "Error on stmt3: " . $stmt3->error;
                                    }
                                    
                                    // Close the statement and connection
                                    $stmt3->close();
                                    $this->conn->close();
                                }
                                
                                // Close the statement and connection
                                $stmt2->close();
                                $this->conn->close();
                                
                            } else {
                                
                                // Close the statement and connection
                                $stmt2->close();
                                $this->conn->close();
                                return "Error on stmt2: " . $stmt2->error;
                            }
                            
                            // Close the statement and connection
                            $stmt2->close();
                            $this->conn->close();

                            break;
                        case 'PENDING':
                            
                            // Prepare the SQL statement with placeholders
                            $sql2 = "UPDATE $programName SET status='ACTIVE', minPrice=?, midPrice=?, maxPrice=?, trialPrice=? WHERE coachName = ? ";
                            // Prepare the statement
                            $stmt2 = $this->conn->prepare($sql2);
                            if ($stmt2 === false) {
                                die("Prepare failed: " . $this->conn->error);
                            }
                            
                            // Bind parameters
                            $stmt2->bind_param("sssss",$minPrice,$midPrice,$maxPrice,$trialPrice,$coachName);
                            // Execute the statement
                            if ($stmt2->execute()) {
                                return 'Success';
                            } else {
                                return "Error on stmt2: " . $stmt2->error;
                            }
                            
                            // Close the statement and connection
                            $stmt2->close();
                            $this->conn->close();
                            break;
                    }
                
                $stmt->close();
                $this->conn->close();
                
            } else {
                $stmt->close();
                $this->conn->close();
                return "Unexpected Error: " . $stmt->error;
            }
    }
    
    //coach Add New Course Cost (OLD)
    /*function coachAddNewCourse(){
        
        $programName = $this->programData->programName;
        $programName = strtolower($programName);
        $programName = str_replace(' ', '', $programName);
        $coachName = $this->programData->coachName;
        $courseName = $this->programData->courseName;
        $courseDesc = $this->programData->courseDesc;
        $courseCategory = $this->programData->courseCategory;
        
        $lowPrice = $this->programData->lowPrice;
        $nosLowPrice = $this->programData->nosLowPrice;
        
        $midPrice = $this->programData->midPrice;
        $nosMidPrice = $this->programData->nosMidPrice;
        
        $highPrice = $this->programData->highPrice;
        $nosHighPrice = $this->programData->nosHighPrice;
        
        $trialPrice = $this->programData->trialPrice;
        $nosTrialPrice = $this->programData->nosTrialPrice;
        
        $studentCategory = $this->programData->studentCategory;
        
        $specificSchool = $this->programData->specificSchool;
        
        if(!empty($trialPrice)){ 
            if(!empty($specificSchool)){
                $sql = "INSERT INTO {$programName} (`coachName`, `status`, `courseName`, `courseDesc`, `courseCategory`, `lowPrice`, `nosLowPrice`, `midPrice`, `nosMidPrice`, `highPrice`, `nosHighPrice`, `trialPrice`, `nosTrialPrice`, `studentCategory`, `specificSchool`) VALUES (?, 'ACTIVE', ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; 
                $stmt = $this->conn->prepare($sql);
                if ($stmt === false) {die("Prepare failed: " . $this->conn->error);} 
                $stmt->bind_param("ssssdidididiss", $coachName, $courseName, $courseDesc, $courseCategory, $lowPrice, $nosLowPrice, $midPrice,$nosMidPrice, $highPrice, $nosHighPrice, $trialPrice, $nosTrialPrice,$studentCategory,$specificSchool); 
            }else{
                $sql = "INSERT INTO {$programName} (`coachName`, `status`, `courseName`, `courseDesc`, `courseCategory`, `lowPrice`, `nosLowPrice`, `midPrice`, `nosMidPrice`, `highPrice`, `nosHighPrice`, `trialPrice`, `nosTrialPrice`, `studentCategory`) VALUES (?, 'ACTIVE', ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; 
                $stmt = $this->conn->prepare($sql); 
                if ($stmt === false) {die("Prepare failed: " . $this->conn->error);} 
                $stmt->bind_param("ssssdidididis", $coachName, $courseName, $courseDesc, $courseCategory, $lowPrice, $nosLowPrice, $midPrice,$nosMidPrice, $highPrice, $nosHighPrice, $trialPrice, $nosTrialPrice,$studentCategory); 
            }
        }else{ 
            if(!empty($specificSchool)){
                $sql = "INSERT INTO {$programName} (`coachName`, `status`, `courseName`, `courseDesc`, `courseCategory`, `lowPrice`, `nosLowPrice`, `midPrice`, `nosMidPrice`, `highPrice`, `nosHighPrice`, `studentCategory`, `specificSchool`) VALUES (?, 'ACTIVE', ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; 
                $stmt = $this->conn->prepare($sql); 
                if ($stmt === false) {die("Prepare failed: " . $this->conn->error);} 
                $stmt->bind_param("ssssdididiss", $coachName, $courseName, $courseDesc, $courseCategory, $lowPrice, $nosLowPrice, $midPrice,$nosMidPrice, $highPrice, $nosHighPrice,$studentCategory,$specificSchool);
            }else{
                $sql = "INSERT INTO {$programName} (`coachName`, `status`, `courseName`, `courseDesc`, `courseCategory`, `lowPrice`, `nosLowPrice`, `midPrice`, `nosMidPrice`, `highPrice`, `nosHighPrice`, `studentCategory`) VALUES (?, 'ACTIVE', ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; 
                $stmt = $this->conn->prepare($sql); 
                if ($stmt === false) {die("Prepare failed: " . $this->conn->error);} 
                $stmt->bind_param("ssssdididis", $coachName, $courseName, $courseDesc, $courseCategory, $lowPrice, $nosLowPrice, $midPrice,$nosMidPrice, $highPrice, $nosHighPrice,$studentCategory);
            }
        }

        // Execute the statement
        if ($stmt->execute()) {
            // Get the last inserted ID 
            $id = $stmt->insert_id;
            
            $stmt = $this->conn->prepare("SELECT alphanumeric_id FROM {$programName} WHERE id = ?"); 
            
            if ($stmt === false) { die("Prepare failed: " . htmlspecialchars($conn->error)); } 
            
            $stmt->bind_param("s", $id); 
            
            $stmt->execute(); 
            
            // Bind result variables 
            $stmt->bind_result($alphanumeric_id);
            
            // Fetch the result 
            if ($stmt->fetch()) { 
                return $alphanumeric_id;
            } else { 
                return false;
            } 

            $stmt->close(); 
            $this->conn->close();
            
        } else {
            $stmt->close();
            $this->conn->close();
            echo "Unexpected Error: " . $stmt->error;
            return false;
        }
    }*/
    
    //coach Add New Course Cost
    function coachAddNewCourse(){
        
        $programName = $this->programData->programName;
        $programName = strtolower($programName);
        $programName = str_replace(' ', '', $programName);
        $coachName = $this->programData->coachName;
        $courseName = $this->programData->courseName;
        $courseDesc = $this->programData->courseDesc;
        $masterLevel = $this->programData->masterLevel;
        $price = $this->programData->price;
        $trialPrice = $this->programData->trialPrice;
        $studentCategory = $this->programData->studentCategory;
        $specificSchool = $this->programData->specificSchool;
        
        $priceType = $this->programData->priceType;
        
        if($priceType == 'default'){
            $status = 'ACTIVE';
        } else if ($priceType == 'custom'){
            $status = 'PENDING';
        }
        
        if(!empty($trialPrice)){ 
            if(!empty($specificSchool)){
                $sql = "INSERT INTO {$programName} (`coachName`, `status`, `courseName`, `courseDesc`, `masterLevel`, `price`, `trialPrice`, `studentCategory`, `specificSchool`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"; 
                $stmt = $this->conn->prepare($sql);
                if ($stmt === false) {die("Prepare failed: " . $this->conn->error);} 
                $stmt->bind_param("sssssddss", $coachName, $status, $courseName, $courseDesc, $masterLevel, $price, $trialPrice,$studentCategory,$specificSchool); 
            }else{
                $sql = "INSERT INTO {$programName} (`coachName`, `status`, `courseName`, `courseDesc`, `masterLevel`, `price`, `trialPrice`, `studentCategory`) VALUES (?, ?, ?, ?, ?, ?, ?, ?)"; 
                $stmt = $this->conn->prepare($sql); 
                if ($stmt === false) {die("Prepare failed: " . $this->conn->error);} 
                $stmt->bind_param("sssssdds", $coachName, $status, $courseName, $courseDesc, $masterLevel, $price, $trialPrice,$studentCategory); 
            }
        }else{ 
            if(!empty($specificSchool)){
                $sql = "INSERT INTO {$programName} (`coachName`, `status`, `courseName`, `courseDesc`, `masterLevel`, `price`, `studentCategory`, `specificSchool`) VALUES (?, ?, ?, ?, ?, ?, ?, ?)"; 
                $stmt = $this->conn->prepare($sql);
                if ($stmt === false) {die("Prepare failed: " . $this->conn->error);} 
                $stmt->bind_param("sssssdss", $coachName, $status, $courseName, $courseDesc, $masterLevel, $price,$studentCategory,$specificSchool);
            }else{
                $sql = "INSERT INTO {$programName} (`coachName`, `status`, `courseName`, `courseDesc`, `masterLevel`, `price`, `studentCategory`) VALUES (?, ?, ?, ?, ?, ?, ?)"; 
                $stmt = $this->conn->prepare($sql);
                if ($stmt === false) {die("Prepare failed: " . $this->conn->error);} 
                $stmt->bind_param("sssssds", $coachName, $status, $courseName, $courseDesc, $masterLevel, $price,$studentCategory);
            }
        }

        // Execute the statement
        if ($stmt->execute()) {
            // Get the last inserted ID 
            $id = $stmt->insert_id;
            
            $stmt = $this->conn->prepare("SELECT alphanumeric_id FROM {$programName} WHERE id = ?"); 
            
            if ($stmt === false) { die("Prepare failed: " . htmlspecialchars($conn->error)); } 
            
            $stmt->bind_param("s", $id); 
            
            $stmt->execute(); 
            
            // Bind result variables 
            $stmt->bind_result($alphanumeric_id);
            
            // Fetch the result 
            if ($stmt->fetch()) { 
                return $alphanumeric_id;
            } else { 
                return false;
            } 

            $stmt->close(); 
            $this->conn->close();
            
        } else {
            $stmt->close();
            $this->conn->close();
            echo "Unexpected Error: " . $stmt->error;
            return false;
        }
    }
    
    
    function coachAddCustomPriceCourse($alphanumeric_id, $price, $masterLevel){
        
        $stmt = $this->conn->prepare("INSERT INTO `courseCustomPriceList`(`alphanumeric_id`, `price`, `masterLevel`) VALUES (?,?,?)");
        if ($stmt === false) { die("Prepare failed: " . htmlspecialchars($conn->error)); }
        $stmt->bind_param("sss", $alphanumeric_id, $price, $masterLevel);
        
        // Execute the statement
        if ($stmt->execute()) {
            // Close the statement and connection
            $stmt->close();
            $this->conn->close();
            return true;
        } else {
            // Close the statement and connection
            $stmt->close();
            $this->conn->close();
            return false;
        }    
    }
    
    //update ID related to course after coachAddNewCourse executed
    function coachNewCourseUpdateID(){
        
        $programName = $this->programData->programName;
        $programName = strtolower($programName);
        $programName = str_replace(' ', '', $programName);

        if ($this->conn->connect_error) { die("Connection failed: " . $this->conn->connect_error); }
        
        $stmt = $this->conn->prepare("UPDATE {$programName} SET courseid = ?, status = 'ACTIVE', priceID = ?, trialPriceID = ? WHERE alphanumeric_id = ?"); 
        if ($stmt === false) { die("Prepare failed: " . htmlspecialchars($conn->error)); } 
        $stmt->bind_param("iiis", $this->bukkuID, $this->priceID, $this->trialPriceID, $this->sku);
        
        // Execute the statement 
        if ($stmt->execute()) { 
            // Close the statement and connection 
            $stmt->close(); 
            $this->conn->close();
            return true;
        } else { 
            // Close the statement and connection 
            $stmt->close(); 
            $this->conn->close();
            return false;
        }
    }
    
    //coach add programs to own
    function coachAddProgramToOwn(){
        
        $username = $this->programData->username;
        $programNameList = $this->programData->programNameList;
        
        // Check connection 
        if ($this->conn->connect_error) {die("Connection failed: " . $this->conn->connect_error); }
        
        // Prepare and bind 
        $stmt = $this->conn->prepare("INSERT INTO `programList`(`programName`, `programCategory`, `coachList`, `status`, `programCategorySetBy`) VALUES (?,?,?,'ACTIVE',?)"); 
        $stmt->bind_param("ssss", $programCategory, $coachList, $uername); 
        
        if ($stmt->execute()) { 
            return true;
        } else { 
            echo "Error: " . $stmt->error;
            return $stmt->error;
        }
        
        $stmt->close(); 
        $this->conn->close();
    }
    
    //coach add custom program to the list
    function coachUpdateProgramList(){
        
        $bukkuGroupID = $this->bukkuGroupID;
        $programName = $this->programData->programName;
        $tableName = strtolower(str_replace(' ', '', $programName));
        $programCategory = $this->programData->programCategory;
        $coachList = array($this->programData->username);
        $json_CoachList = json_encode($coachList);
        $username = $this->programData->username;
        
        // Check connection 
        if ($this->conn->connect_error) {die("Connection failed: " . $this->conn->connect_error); }
        
        // Prepare and bind 
        $stmt = $this->conn->prepare("INSERT INTO `programList`(`bukkuGroupID`, `programName`, `tableName`, `programCategory`, `coachList`, `status`, `programCategorySetBy`) VALUES (?,?,?,?,?,'ACTIVE',?)");
        $stmt->bind_param("isssss", $bukkuGroupID, $programName, $tableName, $programCategory, $json_CoachList, $username); 
        
        if ($stmt->execute()) {
            
            $last_id = $this->conn->insert_id;
            
            $resultCreateTable = $this->createTable($tableName);
            if($resultCreateTable == true){
                return $last_id;
            }else{
                return;
            }
        } else { 
            //echo "Error: " . $stmt->error;
            return;
        }
        
        $stmt->close(); 
        $this->conn->close();
    }
    
    function createTable($tableName) {
        $checkTableSql = "SELECT 1 FROM $tableName LIMIT 1";
        $tableExists = $this->conn->query($checkTableSql);

        if (!$tableExists) {
            
            $sql = "CREATE TABLE $tableName (
                id INT AUTO_INCREMENT PRIMARY KEY,
                alphanumeric_id VARCHAR(10),
                courseid INT,
                coachName VARCHAR(255),
                status VARCHAR(255),
                courseName VARCHAR(255),
                courseDesc VARCHAR(255),
                masterLevel VARCHAR(16),
                bookedSlots LONGTEXT,
                price DOUBLE,
                priceID INT,
                trialPrice DOUBLE,
                trialPriceID INT,
                studentCategory VARCHAR(255),
                specificSchool VARCHAR(255),
                courseCreation TIMESTAMP DEFAULT CURRENT_TIMESTAMP)";
                
                if ($stmt = $this->conn->prepare($sql)) {

                    if ($stmt->execute()) {
                        return true;
                        $this->conn->close();
                    } else {
                        //echo "Error creating table: " . $stmt->error;
                        return $stmt->error;
                    }
                    $stmt->close();
                } else {
                    //echo "Error preparing statement: " . $this->conn->error;
                    return $this->conn->error;
                }
        } else {
            //echo "Table $tableName already exists. No table was created.";
            return;
        }
    }
    
    //need to check is this method used in any other php
    function getProgramID(){
        // Prepare statement
        $sql = "SELECT programID FROM `programList` WHERE programName = ? LIMIT 1";
        if ($stmt = $this->conn->prepare($sql)) {
            // Bind parameters
            $stmt->bind_param("s", $this->programData->programName);

            // Execute statement
            $stmt->execute();

            // Bind result variables
            $stmt->bind_result($programID);

            // Fetch the result
            if ($stmt->fetch()) {
                return $programID;
            } else {
                return;
            }

            // Close statement
            $stmt->close();
        } else {
            return;
        }
        $this->conn->close();
    }
    
    function createTrigger($programName, $programID, $programCategory){
        
        $programName = strtolower(str_replace(' ', '', $programName));
        // Check connection
        if ($this->conn->connect_error) {die("Connection failed: " . $this->conn->connect_error);}
        
        $programCat;
        
        switch ($programCategory){
            case 'Life Skills':
                $programCat = 'L';
                break;
            case 'Development':
                $programCat = 'D';
                break;
            case 'Sports':
                $programCat = 'S';
                break;
        }
        
        $prefix = $programCat . $programID;
        
        $programName = mysqli_real_escape_string($this->conn, $programName);
        $prefix = mysqli_real_escape_string($this->conn, $prefix);
        
        // SQL to create BEFORE INSERT trigger
        $trigger_sql = "
        CREATE TRIGGER before_insert_".$programName."
        BEFORE INSERT ON ".$programName."
        FOR EACH ROW
        BEGIN
            DECLARE prefix VARCHAR(10);
            DECLARE alphanumeric_id VARCHAR(15);

            -- Set the prefix based on the category
            SET prefix = '".$prefix."' ;

            -- Generate the alphanumeric ID (based on max id from the table)
            SET alphanumeric_id = CONCAT(prefix, LPAD((SELECT IFNULL(MAX(id), 0) + 1 FROM ".$programName."), 4, '0'));

            -- Set the alphanumeric_id in the NEW row
            SET NEW.alphanumeric_id = alphanumeric_id;

            -- Set the new ID (auto-increment logic or your custom logic)
            SET NEW.id = (SELECT IFNULL(MAX(id), 0) + 1 FROM ".$programName.");
        END
        ";
        
        // Execute the query to create the trigger
        if (mysqli_query($this->conn, $trigger_sql)) {
            echo "Trigger created successfully.";
            return true;
        } else {
            echo "Error creating trigger: " . mysqli_error($this->conn);
            return;
        }

        // Close the connection
        $this->conn->close();
    }
    
     //coach add programs to own
    function coachEnrollProgram(){
        
        $username = $this->programData->username;
        $programNameList = $this->programData->programNameList;
        
        $programNameArray = explode(",", $programNameList);
        
        // Check connection 
        if ($this->conn->connect_error) {die("Connection failed: " . $this->conn->connect_error); }
        
        $stmt = $this->conn->prepare("UPDATE `programList` SET `coachList`= JSON_ARRAY_APPEND(`coachList`, '$', ?) WHERE `programName`=?");
        
        $successArray = [];
        
        // Iterate through the array and execute the update query for each element 
        foreach ($programNameArray as $programName) {
            
            $stmt->bind_param("ss", $username, $programName); 
            
            if ($stmt->execute()) { 
                $successArray[] = true; 
            } else { 
                $successArray[] = false;
            }
            
        }
        
        // Check if there were any failures 
        if (in_array(false, $successArray)) { 
            return false;
        } else { 
            return true;
        }
        
        $stmt->close(); 
        $this->conn->close();
    }
    
}

?>