<?php
class PriceList{
    
    public $priceListID;
    public $alphanumeric_id;
    public $intermediatePrice;
    public $advancePrice;
    public $masterPrice;
    public $username;
    public $status;
    public $tableName;
    
    public $bukkuID;
    public $priceID;
    public $trialPriceID;
    
    // database connection
    private $conn;
  
    // constructor with $db as database connection
    public function __construct($db){
        $this->conn = $db;
    }
    
    function viewDefaultList(){
        $query = "SELECT * FROM courseDefaultPriceList";
        $result = mysqli_query($this->conn,$query);
        $row = mysqli_fetch_all($result,MYSQLI_ASSOC);
        return $row;
    }
    
    function updateDefaultList(){
        
        $stmt1 = $this->conn->prepare("INSERT INTO courseDefaultPriceList (intermediatePrice, advancePrice, masterPrice, updatedBy) SELECT intermediatePrice, advancePrice, masterPrice, updatedBy FROM courseDefaultPriceList WHERE priceListID = 1");
        
        if($stmt1->execute()){
            $stmt2 = $this->conn->prepare("UPDATE courseDefaultPriceList SET intermediatePrice = ?, advancePrice = ?, masterPrice = ?, updatedBy = ?  WHERE priceListID = 1");
            $stmt2->bind_param("ssss", $this->intermediatePrice, $this->advancePrice, $this->masterPrice, $this->username);
            if ($stmt2->execute()) {
                if ($stmt2->affected_rows > 0) {
                    return true;
                } else {
                    return false;
                }
            } else {
                return false;
            }
        }else{
            return false;
        }
    }
    
    function viewDefaultListForClient(){
        $query = "SELECT * FROM courseDefaultPriceList WHERE priceListID = 1";
        $result = mysqli_query($this->conn,$query);
        $row = mysqli_fetch_all($result,MYSQLI_ASSOC);
        return $row;
    }
    
    function viewCustomList(){
        $query = "SELECT * FROM courseCustomPriceList";
        $result = mysqli_query($this->conn,$query);
        $row = mysqli_fetch_all($result,MYSQLI_ASSOC);
        return $row;
    }
    
    function addCustomList(){
        $stmt1 = $this->conn->prepare("INSERT INTO courseCustomPriceList (alphanumeric_id, intermediatePrice, advancePrice, masterPrice) VALUES (?,?,?,?)");
        $stmt1->bind_param("ssss", $this->alphanumeric_id, $this->intermediatePrice, $this->advancePrice, $this->masterPrice);
        if($stmt1->execute()){
            if ($stmt1->affected_rows > 0) {
                return true;
            } else {
                return false;
            }
        } else {
            return false;
        }
    }
    
    function updateCustomList(){
        
        $stmt1 = $this->conn->prepare("UPDATE courseCustomPriceList SET status = ?, updatedBy = ?  WHERE alphanumeric_id = ?");
        $stmt1->bind_param("sss", $this->status, $this->username, $this->alphanumeric_id);
        if ($stmt1->execute()) {
            if ($stmt1->affected_rows > 0) {
                if($this->status == 'ACTIVE'){
                    $stmt2 = $this->conn->prepare("SELECT alphanumeric_id, courseName, price, trialPrice FROM {$this->tableName} WHERE alphanumeric_id = ?");
                    $stmt2->bind_param("s", $this->alphanumeric_id);
                    $stmt2->execute();
                    $result = $stmt2->get_result();
                    
                    $stmt3 = $this->conn->prepare("SELECT bukkuGroupID FROM programList WHERE tableName = ?");
                    $stmt3->bind_param("s", $this->tableName);
                    $stmt3->execute();
                    $result2 = $stmt3->get_result();
                    
                    $dataArray = []; // Initialize an empty array
                    
                    while ($row = $result->fetch_assoc()) {
                        $dataArray[] = $row;
                    }
                    
                    while ($row2 = $result2->fetch_assoc()) {
                        $dataArray[] = $row2;
                    }
                    
                    return $dataArray;
                } else {
                    return true;
                }
            } else {
                return;
            }
        } else {
            return;
        }
    }
    
    //update ID related to course after coachAddNewCourse executed
    function coachNewCourseUpdateID(){

        if ($this->conn->connect_error) { die("Connection failed: " . $this->conn->connect_error); }
        
        $stmt = $this->conn->prepare("UPDATE {$this->tableName} SET courseid = ?, status = ?, priceID = ?, trialPriceID = ? WHERE alphanumeric_id = ?");
        if ($stmt === false) { die("Prepare failed: " . htmlspecialchars($conn->error)); }
        $stmt->bind_param("isiis", $this->bukkuID, $this->status, $this->priceID, $this->trialPriceID, $this->alphanumeric_id);
        
        // 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 decline status only of custom priced program
    function coachCourseUpdateDeclineStatus(){
        
        if ($this->conn->connect_error) { die("Connection failed: " . $this->conn->connect_error); }
        
        $stmt = $this->conn->prepare("UPDATE {$this->tableName} SET status = ? WHERE alphanumeric_id = ?");
        if ($stmt === false) { die("Prepare failed: " . htmlspecialchars($conn->error)); }
        $stmt->bind_param("ss", $this->status, $this->alphanumeric_id);
        
        // 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;
        }
    }
    
}
?>