<?php
include "emails/email.class.php";

session_start();
class DBMain
{

    public $conn;
    public $table;

    function __construct($db = "", $prefix = 'elegaysv')
    {
        $ip = 'ewg.dedicated.co.za';
        $user = 'Code2';
        $pass = "EWG2Cod!@#";
        $this->conn = mysqli_connect($ip, "elegaysv_" . $user, $pass, "elegaysv_ems");
    }

    function insert($Query = '')
    {

        $this->conn->query($Query);

        return $this->conn->insert_id;
    }

    function login($username, $password)
    {

        $password = hash('sha256', $password);
        $result = $this->conn->query("SELECT * FROM `users` WHERE `username` = '$username' AND `password` = '$password'");
        if ($result->num_rows > 0) {
            $data = $result->fetch_assoc();
            return $data['record_id'];
        } else {
            return false;
        }
    }
    function add_web_notification($message)
    {
        $this->conn->query("INSERT INTO `notifications` (`user_id`, `message`,`message_type`) VALUES ({$_SESSION['user_id']}, '$message',1)");
    }

    function add_webapp_notification($message)
    {
        $this->conn->query("INSERT INTO `notifications` (`user_id`, `message`,`message_type`) VALUES ({$_SESSION['user_id']}, '$message',3)");
    }

    function add_email_notification($message)
    {
        $this->conn->query("INSERT INTO `notifications` (`user_id`, `message`,`message_type`) VALUES ({$_SESSION['user_id']}, '$message',2)");
    }


    function user_notifications($type)
    {
        $result = $this->conn->query("SELECT * FROM `notifications` WHERE `user_id` = {$_SESSION['user_id']} AND `status` = 0 AND message_type = $type ORDER BY record_id ASC");
        if ($result->num_rows > 0) {
            $data = $result->fetch_assoc();
            $this->conn->query("UPDATE `notifications` SET `status` = '1' WHERE `record_id` = {$data['record_id']}");
            return $data['message'];
        } else {
            return false;
        }
    }

    function add_client($name, $contact_person, $number, $email, $address, $google_maps_link, $status)
    {
        $res = $this->conn->query("INSERT INTO `clients`(`name`, `contact_person`, `number`, `email`, `address`, `google_maps_link`, `status`) VALUES ('$name', '$contact_person', '$number', '$email', '$address', '$google_maps_link', '$status')");
        if ($res) {
            self::add_webapp_notification("CLIENT: $name ADDED");
            return 1;
        } else {
            self::add_webapp_notification("CLIENT: $name FAILED TO ADD");
            return 0;
        }
    }

    function edit_client($id, $name, $contact_person, $number, $email, $address, $google_maps_link, $status)
    {

        $res = $this->conn->query("UPDATE `clients` SET `name` = '$name', `contact_person` = '$contact_person', `number` = '$number', `email` = '$email', `address` = '$address', `google_maps_link` = '$google_maps_link', `status` = '$status' WHERE `record_id` = $id");
        if ($res) {
            self::add_webapp_notification("CLIENT: $name updated");
            return 1;
        } else {
            self::add_webapp_notification("CLIENT: $name FAILED TO update");
            return 0;
        }
    }

    function get_client_info($id)
    {
        return $this->conn->query("SELECT * FROM `clients` WHERE `record_id` = $id")->fetch_assoc();
    }


    function get_jobcard_info($record_id)
    {
        $results = $this->conn->query("SELECT * FROM `jobcards` WHERE `record_id` = $record_id");

        return $results->fetch_assoc();
    }


    function get_status($id)
    {
        if ($id == 1) {
            return "ACTIVE";
        } else if ($id == 0) {
            return "OPEN";
        } else {
            return "ON HOLD";
        }
    }

    function add_user($username, $password, $user_type, $status, $name)
    {
        // check if username exists

        $result = $this->conn->query("SELECT * FROM `users` WHERE `username` = '$username'");

        if ($result->num_rows > 0) {
            self::add_webapp_notification("USERname: $username Already exists");
            return 0;
        }

        $password = hash('sha256', $password);
        $date = date('Y-m-d');
        $res = $this->conn->query("INSERT INTO `users`(`username`, `password`, `user_type`, `status`,`date_added`,`name`) VALUES ('$username', '$password', '$user_type', '$status','$date','$name')");

        if ($res) {
            self::add_webapp_notification("USER: $username ADDED");
            return 1;
        } else {
            self::add_webapp_notification("USER: $username FAILED TO ADD : ");
            return 0;
        }
    }

    function edit_user($id, $username, $password, $user_type, $status, $name)
    {

        if ($password != "") {
            $password = hash('sha256', $password);
            $password = "`password` = '$password',";
        }
        $res = $this->conn->query("UPDATE `users` SET `username` = '$username', $password `user_type` = '$user_type', `status` = '$status', `name` = '$name' WHERE `record_id` = $id");
        if ($res) {
            self::add_webapp_notification("USER: $username updated");
            return 1;
        } else {
            self::add_webapp_notification("USER: $username FAILED TO update");
            return 0;
        }
    }

    function get_user_info($id)
    {
        return $this->conn->query("SELECT * FROM `users` WHERE `record_id` = $id")->fetch_assoc();
    }

    function get_new_jobcard_number()
    {

        $result = $this->conn->query("SELECT * FROM `jobcards` ORDER BY `record_id` DESC LIMIT 1");
        $data = $result->fetch_assoc();

        if ($data['jobcard_number'] == null) {

            return 1;
        } else {

            return $data['jobcard_number'] + 1;
        }
    }

    function get_clients_in_dropdown()
    {

        $result = $this->conn->query("SELECT * FROM `clients` ORDER BY `name` ASC");

        if ($result->num_rows > 0) {

            while ($data = $result->fetch_assoc()) {

                echo "<option value='" . $data['record_id'] . "'>" . $data['name'] . "</option>";
            }
        }
    }

    function get_artisans_in_dropdown()
    {
        $result = $this->conn->query("SELECT * FROM `users` WHERE `user_type` = 3 ORDER BY `name` ASC");

        if ($result->num_rows > 0) {

            while ($data = $result->fetch_assoc()) {

                echo "<option value='" . $data['record_id'] . "'>" . $data['name'] . "</option>";
            }
        }
    }

    function get_user_name($user_id)
    {

        $result = $this->conn->query("SELECT * FROM `users` WHERE `record_id` = $user_id");
        return $result->fetch_assoc()['name'];
    }

    function create_jobcard($jobcard_number, $date, $client, $description, $jobcard_user_1, $jobcard_user_2, $jobcard_type, $quote_number)
    {

        $date_now = date("Y-m-d");
        $datetime_now = date("Y-m-d H:i", strtotime("+2 Hours"));

        $this->conn->query("INSERT INTO `jobcards` (`jobcard_number`, `date`,`datetime_created`, `client_id`, `description`,`jobcard_type`,`status`,`user_created_id`,`quote_number`) VALUES ('$jobcard_number', '$date','$date_now','$client', '$description', '$jobcard_type','PENDING','{$_SESSION['user_id']}','$quote_number')");
        $new_id = $this->conn->insert_id;
        $res = $new_id;

        if ($res) {
            self::add_webapp_notification("JOB CARD: $jobcard_number CREATED [$res]");
            $this->conn->query("INSERT INTO `jobcard_users` (`user_id`, `jobcard_id`) VALUES ('$jobcard_user_1', '$new_id')");
            $this->conn->query("INSERT INTO `jobcard_users` (`user_id`, `jobcard_id`) VALUES ('$jobcard_user_2', '$new_id')");
            $this->conn->query("INSERT INTO `jobcard_timeline` (`jobcard_id`,`datetime`,`reason`,`user_id`) VALUES ($new_id,'$datetime_now','PENDING',{$_SESSION['user_id']})");
            return 1;
        } else {
            self::add_webapp_notification("JOB CARD: $jobcard_number FAILED TO CREATE [$res]");
            return 0;
        }
    }

    function sort_page($date, $artisan_id)
    {

        $results = $this->conn->query("SELECT `jobcards`.`record_id`,`jobcards`.`client_id`,`jobcards`.`status`,`jobcards`.`date`,`jobcards`.`jobcard_type` FROM `jobcards` LEFT JOIN `jobcard_users` ON `jobcard_users`.`jobcard_id` = `jobcards`.`record_id` WHERE `jobcards`.`date` = '$date' AND `jobcard_users`.`user_id` = $artisan_id AND `jobcards`.`status` = 1 ORDER BY `jobcard_number` ASC");

        if ($results->num_rows > 0) {
            while ($data = $results->fetch_assoc()) {
                self::build_jobcard_sort_page($data);
            }
        }

        $results = $this->conn->query("SELECT `jobcards`.`record_id`,`jobcards`.`client_id`,`jobcards`.`status`,`jobcards`.`date`,`jobcards`.`jobcard_type` FROM `jobcards` LEFT JOIN `jobcard_users` ON `jobcard_users`.`jobcard_id` = `jobcards`.`record_id` WHERE `jobcards`.`date` = '$date' AND `jobcard_users`.`user_id` = $artisan_id AND `jobcards`.`status` = 2 ORDER BY `jobcard_number` ASC");

        if ($results->num_rows > 0) {
            while ($data = $results->fetch_assoc()) {
                self::build_jobcard_sort_page($data);
            }
        }

        $results = $this->conn->query("SELECT `jobcards`.`record_id`,`jobcards`.`client_id`,`jobcards`.`status`,`jobcards`.`date`,`jobcards`.`jobcard_type` FROM `jobcards` LEFT JOIN `jobcard_users` ON `jobcard_users`.`jobcard_id` = `jobcards`.`record_id` WHERE `jobcards`.`date` = '$date' AND `jobcard_users`.`user_id` = $artisan_id AND `jobcards`.`status` = 0 ORDER BY `jobcard_number` ASC");

        if ($results->num_rows > 0) {
            while ($data = $results->fetch_assoc()) {
                self::build_jobcard_sort_page($data);
            }
        }

        $results = $this->conn->query("SELECT `jobcards`.`record_id`,`jobcards`.`client_id`,`jobcards`.`status`,`jobcards`.`date`,`jobcards`.`jobcard_type` FROM `jobcards` LEFT JOIN `jobcard_users` ON `jobcard_users`.`jobcard_id` = `jobcards`.`record_id` WHERE `jobcards`.`date` = '$date' AND `jobcard_users`.`user_id` = $artisan_id AND `jobcards`.`status` = 3 ORDER BY `jobcard_number` ASC");

        if ($results->num_rows > 0) {
            while ($data = $results->fetch_assoc()) {
                self::build_jobcard_sort_page($data);
            }
        }
    }

    function hold_jobcard($record_id)
    {

        $res = $this->conn->query("UPDATE `jobcards` SET `status` = 2 WHERE `record_id` = $record_id");
        if ($res) {
            $date_now = date("Y-m-d H:i", strtotime("+2 Hours"));
            $this->conn->query("INSERT INTO `jobcard_timeline` (`jobcard_id`,`datetime`,`reason`,`user_id`) VALUES ($record_id,'$date_now','HOLD',{$_SESSION['user_id']})");
            self::add_webapp_notification("JOB CARD HAS BEEN PUT ON HOLD");
            return 1;
        } else {
            self::add_webapp_notification("JOB CARD HAS FAILED BEING PUT ON HOLD");
            return 0;
        }
    }

    function build_jobcard_sort_page($data)
    {
        $client = $this->conn->query("SELECT * FROM `clients` WHERE `record_id` = '{$data['client_id']}'")->fetch_assoc();
        echo "<div class='flex_row' id='container_of_jc-$data[record_id]'>";
        echo "<div class='flex_column'>";
        if ($data['status'] == 0) {
            // pending
            $style = "style=' background-color: grey; color:white;";
        }
        if ($data['status'] == 1) {
            // open
            $style = "style=' background-color: green; color:white;";
        }
        if ($data['status'] == 2) {
            // hold
            $style = "style=' background-color: orange; color:white;";
        }
        if ($data['status'] == 3) {
            // closed
            $style = "style=' background-color: black; color:white;";
        }
        if ($data['jobcard_type'] == "BREAKDOWN") {
            $added_styles = "border:2px solid #ff4500;";
        } else {
            $added_styles = "";
        }
        if ($data['date'] == date("Y-m-d")) {
            echo "<input type='submit' readonly onclick='show_settings($data[record_id])' value='$client[name]' $style $added_styles  width:100%;' />";
            if ($data['status'] != 1 && $data['status'] != 3) {
                echo "<input type='text' readonly value='>' onclick='move_up($data[record_id])'  style='padding:0px;width:3vw;text-alight:center; font-size:1.5vw; background-color: #145593; color:white'/>";
            }
        } else {
            if ($data['status'] != 1 && $data['status'] != 3) {
                echo "<input type='submit' readonly value='<' onclick='move_back($data[record_id])' style='padding:0px;width:3vw;text-alight:center; font-size:1.5vw; background-color: #145593; color:white'/>";
            }
            echo "<input type='submit' readonly value='$client[name]' $style $added_styles  width:100%;' onclick='show_settings($data[record_id])'/>";
            if ($data['status'] != 1 && $data['status'] != 3) {
                echo "<input type='submit' readonly value='>'  style='padding:0px;width:3vw;text-alight:center; font-size:1.5vw; background-color: #145593; color:white' onclick='move_up($data[record_id])'/>";
            }
        }
        echo "</div>";
        echo "<div class='flex_row' style='display:none;' id='jobcard-$data[record_id]'>";
        echo "<input type='submit' value='DETAILS' onclick='view_details($data[record_id])' style='width:45%; margin-right:5%; background-color: #145593;'/>";
        if ($data['status'] == 2) {
            echo "<input type='submit' value='RE-OPEN' onclick='re_open_jobcard($data[record_id])' style='width:45%; margin-left:5%; background-color: green;'/>";
        }
        if ($data['status'] == 0 || $data['status'] == 1) {
            echo "<input type='submit' value='HOLD' onclick='hold_jobcard($data[record_id])' style='width:45%; margin-left:5%; background-color: orange;'/>";
        }
        if ($data['status'] == 3) {
            echo "<input type='submit' value='CLOSED' style='width:45%; margin-left:5%; background-color: black;'/>";
        }
        echo "<input type='submit' value='HIDE' onclick='hide_settings($data[record_id])' style='width:100%;'/>";
        echo "</div>";
        echo "</div>";
    }
    function re_open_jobcard($record_id)
    {

        $res = $this->conn->query("UPDATE `jobcards` SET `status` = 0 WHERE `record_id` = $record_id");
        if ($res) {
            $date_now = date("Y-m-d H:i", strtotime("+2 Hours"));
            $this->conn->query("INSERT INTO `jobcard_timeline` (`jobcard_id`,`datetime`,`reason`,`user_id`) VALUES ($record_id,'$date_now','PENDING',{$_SESSION['user_id']})");
            self::add_webapp_notification("JOB CARD HAS BEEN RE-OPENED");
            return 1;
        } else {
            self::add_webapp_notification("JOB CARD HAS FAILED BEING RE-OPENED");
            return 0;
        }
    }
    function move_jobcard($record_id, $direction)
    {

        $result = $this->conn->query("SELECT * FROM `jobcards` WHERE `record_id` = $record_id")->fetch_assoc();
        $date_now = date("Y-m-d H:i", strtotime("+2 Hours"));

        if ($direction == "up") {
            $old_date = $result['date'];
            // add 1 day to old_date
            $new_date = date('Y-m-d', strtotime($old_date . ' + 1 days'));
            $res = $this->conn->query("UPDATE `jobcards` SET `date` = '$new_date' WHERE `record_id` = $record_id");
            if ($res) {
                $date_now = date("Y-m-d H:i", strtotime("+2 Hours"));
                $this->conn->query("INSERT INTO `jobcard_timeline` (`jobcard_id`,`datetime`,`reason`,`user_id`) VALUES ($record_id,'$date_now','MOVED FROM $old_date TO $new_date',{$_SESSION['user_id']})");
                return 1;
            } else {
                return 0;
            }
        } else {
            $old_date = $result['date'];
            // add 1 day to old_date
            $new_date = date('Y-m-d', strtotime($old_date . ' - 1 days'));
            $res = $this->conn->query("UPDATE `jobcards` SET `date` = '$new_date' WHERE `record_id` = $record_id");
            if ($res) {
                $this->conn->query("INSERT INTO `jobcard_timeline` (`jobcard_id`,`datetime`,`reason`,`user_id`) VALUES ($record_id,'$date_now','MOVED FROM $old_date TO $new_date',{$_SESSION['user_id']})");

                return 1;
            } else {
                return 0;
            }
        }
    }

    function sort_page_overdue($artisan_id)
    {
        $date = date("Y-m-d");
        $results = $this->conn->query("SELECT `jobcards`.`record_id`,`jobcards`.`client_id`,`jobcards`.`status`,`jobcards`.`jobcard_type` FROM `jobcards` LEFT JOIN `jobcard_users` ON `jobcard_users`.`jobcard_id` = `jobcards`.`record_id` WHERE `jobcards`.`date` < '$date' AND `jobcard_users`.`user_id` = $artisan_id ORDER BY `jobcard_number` ASC");

        if ($results->num_rows > 0) {
            while ($data = $results->fetch_assoc()) {
                $client = $this->conn->query("SELECT * FROM `clients` WHERE `record_id` = '{$data['client_id']}'")->fetch_assoc();
                echo "<div class='flex_row' id='container_of_jc-$data[record_id]'>";

                echo "<div class='flex_column'>";
                if ($data['status'] == 0) {
                    // pending
                    $style = "style=' background-color: grey; color:white;";
                }
                if ($data['status'] == 1) {
                    // open
                    $style = "style=' background-color: green; color:white;";
                }
                if ($data['status'] == 2) {
                    // hold
                    $style = "style=' background-color: orange; color:white;";
                }
                if ($data['status'] == 3) {
                    // closed
                    $style = "style=' background-color: black; color:white;";
                }
                if ($data['jobcard_type'] == "BREAKDOWN") {
                    $added_styles = "border:2px solid #ff4500;";
                } else {
                    $added_styles = "";
                }

                echo "<input type='submit' readonly onclick='show_settings($data[record_id])' value='$client[name]' $style $added_styles; width:100%;' /><input type='submit' readonly value='>'  onclick='move_up($data[record_id])' sstyle='padding:0px;width:2vw;text-alight:center; font-size:1.5vw; background-color: #145593; color:white'/>";
                echo "</div>";
                echo "<div class='flex_row' style='display:none;' id='jobcard-$data[record_id]'>";
                echo "<input type='submit' value='DETAILS' onclick='view_details($data[record_id])' style='width:45%; margin-right:5%; background-color: #145593;'/>";
                echo "<input type='submit' value='HOLD' onclick='view_details($data[record_id])' style='width:45%; margin-left:5%; background-color: orange;'/>";
                echo "<input type='submit' value='HIDE' onclick='hide_settings($data[record_id])' style='width:100%;'/>";
                echo "</div>";
                echo "</div>";
            }
        }
    }

    function add_tools($tool_name, $tool_brand, $qr_code)
    {

        $date = date('Y-m-d H:i', strtotime("+2 Hours"));
        $res = $this->conn->query("INSERT INTO `tools`(`date_time_added`,`name`, `brand`, `qr_code`) VALUES ('$date','$tool_name', '$tool_brand', '$qr_code')");
        if ($res) {
            self::add_webapp_notification("TOOL: $tool_name ADDED");
            return 1;
        } else {
            self::add_webapp_notification("TOOL: $tool_name FAILED TO ADD");
            return 0;
        }
    }

    function edit_tools($id, $tools_name, $brand, $qr_code)
    {

        $date = date('Y-m-d H:i', strtotime("+2 Hours"));
        $res = $this->conn->query("UPDATE `tools` SET `date_time_added` = '$date',  `name` = '$tools_name', `brand` = '$brand', `qr_code` = '$qr_code'  WHERE `record_id` = $id");
        // ECHO "UPDATE `tools` SET `date_time_added` = '$date',  `name` = '$tools_name', `brand` = '$brand', `qr_code` = '$qr_code'  WHERE `record_id` = $id";

        if ($res) {
            self::add_webapp_notification("TOOLS: $tools_name updated");
            return 1;
        } else {
            self::add_webapp_notification("TOOLS: $tools_name FAILED TO update");
            return 0;
        }
    }

    function check_qr($qr_code): void
    {
        $result = $this->conn->query("SELECT * FROM `tools` ORDER BY `name` ASC");

        if ($result == $qr_code) {
            echo "QR Code Already Exist, PLease try again";
        }
    }



    function get_brands_in_dropdown()
    {
        $result = $this->conn->query("SELECT * FROM `tools` ORDER BY `name` ASC");

        if ($result->num_rows > 0) {

            while ($data = $result->fetch_assoc()) {

                echo "<option value='" . $data['record_id'] . "'>" . $data['brand'] . "</option>";
            }
        }
    }

    function get_names_in_dropdown()
    {
        $result = $this->conn->query("SELECT * FROM `tools` ORDER BY `name` ASC");

        if ($result->num_rows > 0) {

            while ($data = $result->fetch_assoc()) {

                echo "<option value='" . $data['record_id'] . "'>" . $data['name'] . "</option>";
            }
        }
    }

    function get_tools_info($record_id)
    {
        $results = $this->conn->query("SELECT * FROM `tools` WHERE `record_id` = $record_id");

        return $results->fetch_assoc();
    }

    function get_category_info($category_id)
    {
        $results = $this->conn->query("SELECT * FROM `tool_categories` WHERE `record_id` = $category_id");

        echo '"SELECT * FROM `tool_categories` WHERE `record_id` = $category_id"';

        if ($results->num_rows > 0) {

            while ($data = $results->fetch_assoc()) {
                echo $data['name'];
            }
        }
    }

    function get_tools_category_info($record_id)
    {
        $results = $this->conn->query("SELECT * FROM `tool_categories` WHERE `record_id` = $record_id");

        return $results->fetch_assoc();
    }

    function get_stock_category_info($record_id)
    {
        $results = $this->conn->query("SELECT * FROM `stock_categories` WHERE `record_id` = $record_id");

        // echo'"SELECT * FROM `stock_categories` WHERE `record_id` = $record_id"';

        return $results->fetch_assoc();
    }

    function get_tools_info_from_qr($qr_code)
    {
        $results = $this->conn->query("SELECT * FROM `tools` WHERE `qr_code` = '$qr_code'");

        return $results->fetch_assoc();
    }

    function get_tool_booking_status($record_id = '', $qr_code = '')
    {

        if (strlen($record_id) >= 1) {
            $where_data = "WHERE tool_id = $record_id";
        } else {
            $where_data = "WHERE qr_code = '$qr_code'";
        }

        $results = $this->conn->query("SELECT * FROM `tool_tracking` $where_data ORDER BY record_id DESC LIMIT 1");
        // echo "SELECT * FROM `tool_tracking` $where_data ORDER BY record_id DESC LIMIT 1";
        if ($results->num_rows < 1) {
            return 0;
        } else {
            $data = $results->fetch_assoc();
            return $data['booking_type'] ? $data['booking_type'] : 1;
        }
    }
    function book_tools($qr_code, $artisan_id)
    {
        $date = date("Y-m-d H:i", strtotime("+2 Hours"));
        $current_user_id = $_SESSION['user_id'];
        $tool = self::get_tools_info_from_qr($qr_code);

        if (self::get_tool_booking_status($tool['record_id']) == 1) {
            $status = 1;
        } else {
            $status = 0;
        }

        $res = $this->conn->query("INSERT INTO `tool_tracking`(`date_time_of_booking`,`tool_id`, `user_booked_to_id`, `user_id`,`booking_type`) VALUES ('$date','$tool[record_id]', '$artisan_id', '$current_user_id',$status)");
        // echo "INSERT INTO `tool_tracking`(`date_time_of_booking`,`tool_id`, `user_booked_to_id`, `user_id`,`booking_type`) VALUES ('$date','$tool[record_id]', '$artisan_id', '$current_user_id',$status)";
        if ($res) {
            if ($status == 0) {
                self::add_webapp_notification("TOOL: {$tool['name']} BOOKED OUT");
            } else {
                self::add_webapp_notification("TOOL: {$tool['name']} ADDED");
            }
            return 1;
        } else {
            self::add_webapp_notification("TOOL: {$tool['name']} FAILED TO BOOK");
            return 0;
        }
    }

    function add_stock($stock_name, $description, $qr_code, $shelf, $row, $category)
    {

        $date = date('Y-m-d H:i', strtotime("+2 Hours"));
        $user_id = $_SESSION['user_id'];
        $res = $this->conn->query("INSERT INTO `stock_items`(`date_time_added`,`name`, `description`, `qr_code`, `shelf`, `row`, `user_id_added`, `category`) VALUES ('$date','$stock_name', '$description', '$qr_code', '$shelf', '$row', '$user_id','$category')");
        if ($res) {
            self::add_webapp_notification("STOCK: $stock_name ADDED");
            return 1;
        } else {
            self::add_webapp_notification("STOCK: $stock_name FAILED TO ADD");
            return 0;
        }
    }

    function get_names_in_user()
    {
        $result = $this->conn->query("SELECT * FROM `stock_items` ORDER BY `name` ASC");

        if ($result->num_rows > 0) {

            while ($data = $result->fetch_assoc()) {

                echo "<option value='" . $data['record_id'] . "'>" . $data['user_id_added'] . "</option>";
            }
        }
    }

    function get_names_in_shelf()
    {
        $result = $this->conn->query("SELECT * FROM `stock_items` ORDER BY `name` ASC");

        if ($result->num_rows > 0) {

            while ($data = $result->fetch_assoc()) {

                echo "<option value='" . $data['record_id'] . "'>" . $data['shelf'] . "</option>";
            }
        }
    }

    function get_names_in_row()
    {
        $result = $this->conn->query("SELECT * FROM `stock_items` ORDER BY `name` ASC");

        if ($result->num_rows > 0) {

            while ($data = $result->fetch_assoc()) {

                echo "<option value='" . $data['record_id'] . "'>" . $data['row'] . "</option>";
            }
        }
    }

    function edit_stock($id, $stock_name, $description, $qr_code, $shelf, $row, $category)
    {

        $date = date('Y-m-d H:i', strtotime("+2 Hours"));
        $res = $this->conn->query("UPDATE `stock_items` SET `name` = '$stock_name', `description` = '$description', `qr_code` = '$qr_code', `shelf` = '$shelf', `row` = '$row', `date_time_added` = '$date', `category_id` = $category  WHERE `record_id` = $id");
        // echo "UPDATE `stock_items` SET `name` = '$stock_name', `description` = '$description', `qr_code` = '$qr_code', `shelf` = '$shelf', `row` = '$row', `date_time_added` = '$date'  WHERE `record_id` = $id";

        if ($res) {
            self::add_webapp_notification("STOCK: $stock_name updated");
            return 1;
        } else {
            self::add_webapp_notification("STOCK: $stock_name FAILED TO update");
            return 0;
        }
    }

    function get_stock_info($record_id)
    {
        $results = $this->conn->query("SELECT * FROM `stock_items` WHERE `record_id` = $record_id");

        return $results->fetch_assoc();
    }

    function get_total_info($stock_id)
    {
        $result = $this->conn->query("SELECT * FROM `stock_control` WHERE `stock_id` = $stock_id");

        $total = 0;
        if ($result->num_rows < 1) {
            return 0;
        }
        while ($data = $result->fetch_assoc()) {
            $total = $total + (int) $data['quantity'];
        }
        return $total;

        // echo $results;
    }

    function check_stock($stock_id, $quantity)
    {
        $date = date("Y-m-d H:i", strtotime("+2 Hours"));
        $user_id = $_SESSION['user_id'];

        $current_tot = self::get_total_info($stock_id);
        $stock_check = $current_tot + $quantity;
        if ($stock_check < 0) {
            self::add_webapp_notification("NOT ENOUGH IN STOCK");
            return "O";
        } else {
            $res = $this->conn->query("INSERT INTO `stock_control`(`date`,`user_id`, `stock_id`, `quantity`) VALUES ('$date','$user_id', '$stock_id', '$quantity')");
            if ($res) {
                self::add_webapp_notification("BOOKED");
                return 1;
            } else {
                self::add_webapp_notification("FAILED TO BOOK");
                return 0;
            }
        }
    }

    function find_stock_id($qr_code)
    {
        $result = $this->conn->query("SELECT * FROM `stock_items` WHERE `qr_code` = '$qr_code'");

        if ($result->num_rows > 0) {
            $data = $result->fetch_assoc();
            return $data['record_id'] . "," . self::get_total_info($data['record_id']);
        }
    }

    function find_tool_id($qr_code)
    {
        $result = $this->conn->query("SELECT * FROM `tools` WHERE `qr_code` = '$qr_code'");

        if ($result->num_rows > 0) {
            $data = $result->fetch_assoc();
            return $data['record_id'];
        }
    }

    function get_tool_category_list()
    {
        $results = $this->conn->query("SELECT * FROM `tool_categories` ORDER BY `name` ASC");
        if ($results->num_rows < 1) {
            echo "<option></option>";
        } else {
            $list = '';
            while ($row = $results->fetch_assoc()) {
                $list = $list . "<option value='{$row['record_id']}'>{$row['name']}</option>";
            }
            echo $list;
        }
    }

    function get_stock_category_list()
    {
        $results = $this->conn->query("SELECT * FROM `stock_categories` ORDER BY `name` ASC");
        if ($results->num_rows < 1) {
            echo "<option></option>";
        } else {
            $list = '';
            while ($row = $results->fetch_assoc()) {
                $list = $list . "<option value='{$row['record_id']}'>{$row['name']}</option>";
            }
            echo $list;
        }
    }

    function add_stock_category($name)
    {

        $res = $this->conn->query("INSERT INTO `stock_categories`(`name`) VALUES ('$name')");
        if ($res) {
            self::add_webapp_notification("STOCK CATEGORY: $name ADDED");
            return 1;
        } else {
            self::add_webapp_notification("STOCK CATEGORY: $name FAILED TO ADD");
            return 0;
        }
    }

    function add_tool_category($name)
    {

        $res = $this->conn->query("INSERT INTO `tool_categories`(`name`) VALUES ('$name')");
        if ($res) {
            self::add_webapp_notification("TOOL CATEGORY: $name ADDED");
            return 1;
        } else {
            self::add_webapp_notification("TOOL CATEGORY: $name FAILED TO ADD");
            return 0;
        }
    }

    function edit_stock_category($id, $name)
    {

        $date = date('Y-m-d H:i', strtotime("+2 Hours"));
        $res = $this->conn->query("UPDATE `stock_categories` SET `name` = '$name'  WHERE `record_id` = $id");

        if ($res) {
            self::add_webapp_notification("STOCK: $name UPDATED");
            return 1;
        } else {
            self::add_webapp_notification("STOCK: $name FAILED TO update");
            return 0;
        }
    }

    function edit_tool_category($id, $name)
    {

        $res = $this->conn->query("UPDATE `tool_categories` SET `name` = '$name' WHERE `record_id` = $id");
        if ($res) {
            self::add_webapp_notification("TOOL CATEGORIE: $name UPDATED");
            return 1;
        } else {
            self::add_webapp_notification("TOOL CATEGORIE: $name FAILED TO update");
            return 0;
        }
    }

    function generate_quote_number()
    {

        $results = $this->conn->query("SELECT * FROM `quotes` ORDER BY `record_id` DESC LIMIT 1");

        $data = $results->fetch_assoc();
        if ($results->num_rows < 1) {
            return 1;
        } else {
            return $data['quote_number'] + 1;
        }
    }


    function add_quote($data)
    {
        $date = date('Y-m-d H:i', strtotime("+2 Hours"));
        $user_id = $_SESSION['user_id'];
        $client_id = $data->client_id;
        $quote_number = $data->quote_number;
        $description = $data->description;
        $items = implode("|", $data->items);
        $prices = implode("|", $data->prices);
        $amounts = implode("|", $data->amounts);
        $Query = "INSERT INTO `quotes`(`record_id`, `quote_number`, `description`, `client_id`, `date_time_created`, `date_time_accepted`, `user_created`, `stock_user_verified`, `admin_verified`, `approved`, `stock_verified_date_time`, `admin_verified_date_time`, `approved_date_time`, `sent_date_time`, `sent`, `items`, `prices`, `amount`) VALUES (NULL, '$quote_number', '$description', '$client_id', '$date', '1900-01-01 00:00:00', '$user_id', 0, 0, 0, '1900-01-01 00:00:00', '1900-01-01 00:00:00', '1900-01-01 00:00:00', '1900-01-01 00:00:00', 0, '$items', '$prices', '$amounts')";
        $res = $this->conn->query($Query);

        if ($res) {
            self::add_webapp_notification("QUOTE: $quote_number ADDED");
            // return 1;
        } else {
            self::add_webapp_notification("QUOTE: $quote_number FAILED TO ADD");
            // return 0;
        }
    }

    function get_quote_by_id($id)
    {

        return $this->conn->query("SELECT * FROM `quotes` WHERE `record_id` = $id")->fetch_assoc();

    }

    function update_quote($data)
    {
        $date = date('Y-m-d H:i', strtotime("+2 Hours"));
        $user_id = $_SESSION['user_id'];
        $client_id = isset($data->client_id) ? $data->client_id : null;
        $quote_number = isset($data->quote_number) ? $data->quote_number : null;
        $description = isset($data->description) ? $data->description : '';
        $items = isset($data->items) ? implode("|", $data->items) : '';
        $prices = isset($data->prices) ? implode("|", $data->prices) : '';
        $amount = isset($data->amounts) ? implode("|", $data->amounts) : '';

        $Query = "UPDATE `quotes` SET ";
        if (isset($data->sent)) {

            $Query .= " `approved` = 1 , `sent` = 1, ";
            $Query = rtrim($Query, ', ');
            $Query .= " WHERE `record_id` = $data->record_id";
            $this->conn->query($Query);
            self::add_webapp_notification("QUOTE: $quote_number UPDATED");
            $client_info = self::get_client_info($client_id);
            $email = new email();
            $email->send_mail(
                ['info@emsshop.co.za'],
                ['info@emsshop'],
                "<html>
                    <body>
                                <h1 style='color:blue; font-weight: bold; font-size: 10vw'>Electrical Master Solutions (Pty) Ltd<h1>
                                <br><br>
                                <p style='color:black; ' >Dear {$client_info['name']},</p>
                                <p style='color:black; ' >Thank you for reaching out. Attached is the quote as requested.</p>

                    </body>
                </html>"
                ,
                "QUOTE: $quote_number"
                ,
                '../../app/quotes/quote.pdf',
                1
            );
            return 1;
        }
        if (isset($data->client_id)) {
            $Query .= "`client_id` = '$client_id', ";
        }
        if (isset($data->quote_number)) {
            $Query .= "`quote_number` = '$quote_number', ";
        }
        if (isset($data->description)) {
            $Query .= "`description` = '$description', ";
        }
        if (isset($data->items)) {
            $Query .= "`items` = '$items', ";
        }
        if (isset($data->prices)) {
            $Query .= "`prices` = '$prices', ";
        }
        if (isset($data->stock)) {
            $Query .= "`stock_user_verified` = '$data->stock', ";
        }
        if (isset($data->admin)) {
            $Query .= "`admin_verified` = '$data->admin', ";
        }
        if (isset($data->amounts)) {
            $Query .= "`amount` = '$amount', ";
        }

        $Query = rtrim($Query, ', ');
        $Query .= " WHERE `record_id` = $data->record_id";
        $res = $this->conn->query($Query);
  
        if ($res) {
            self::add_webapp_notification("QUOTE: $quote_number UPDATED");
            return 1;
        } else {
            self::add_webapp_notification("QUOTE: $quote_number FAILED TO UPDATE");
            return 0;
        }
    }
}