<?php

class db
{

    private $connection;
    private $sql;
    private $table_name;

    public function __construct($host = "ewg.dedicated.co.za", $user = 'elegaysv_Code2', $password = 'EWG2Cod!@#', $dbname = 'elegaysv_edesigns')
    {
        $this->connection = mysqli_connect($host, $user, $password, $dbname);

        // auto creates logs

        // auto creates logs
        if (!$this->check_table_exists('logs')) {
            $sql = "CREATE TABLE IF NOT EXISTS logs (
                record_id INT AUTO_INCREMENT PRIMARY KEY,
                table_name TEXT,
                user_id INT(255),
                query TEXT,
                date_time VARCHAR(50)
            )";

            if (!mysqli_query($this->connection, $sql)) {
                return "Error creating table: " . mysqli_error($this->connection);
                exit();
            }
        }

        // auto create users
        if (!$this->check_table_exists('users')) {
            $sql = "CREATE TABLE IF NOT EXISTS users (
                record_id INT AUTO_INCREMENT PRIMARY KEY,
                username TEXT,
                email TEXT,
                initials TEXT,
                `type` TEXT,
                date_time_added VARCHAR(50),
                `status` int(1) DEFAULT 1,
                `password` TEXT
            )";

            if (!mysqli_query($this->connection, $sql)) {
                return "Error creating table: " . mysqli_error($this->connection);
                exit();
            }

            $sql = "INSERT INTO users (username, `password`,`status`,`type`) VALUES ('DEV', '4030fe15babb7045f9036c2316babda746af34b61e623354c61828526c4e2ad5',1,1)";

            if (!mysqli_query($this->connection, $sql)) {
                return "Error inserting first user: " . mysqli_error($this->connection);
                exit();
            }
        }

        // auto create user_types
        if (!$this->check_table_exists('user_types')) {
            $sql = "CREATE TABLE IF NOT EXISTS user_types (
                record_id INT(255) AUTO_INCREMENT PRIMARY KEY,
                `name` TEXT,
                date_time_added VARCHAR(50) DEFAULT CURRENT_TIMESTAMP,
                user_id_added INT(255),
                admin_access INT(1) DEFAULT 0
            )";
            if (!mysqli_query($this->connection, $sql)) {
                return "Error creating table: " . mysqli_error($this->connection);
                exit();
            }
            $sql = "INSERT INTO user_types (`name`, user_id_added) VALUES ('ADMIN', '1')";
            if (!mysqli_query($this->connection, $sql)) {
                return "Error inserting first user: " . mysqli_error($this->connection);
                exit();
            }
        }

        // auto create user_table_views
        if (!$this->check_table_exists('user_table_views')) {
            $sql = "CREATE TABLE IF NOT EXISTS user_table_views (
                record_id INT(255) AUTO_INCREMENT PRIMARY KEY,
                `user_id` INT(255),
                `table_name` TEXT,
                `column_name` TEXT,
                `match_to_table` TEXT,
                `pull_column` TEXT,
                `function` TEXT,
                `display_method` TEXT,
                `onclick` TEXT,
                `onclick_file` TEXT,
                `icon` TEXT,
                `custom_header` TEXT,
                `custom_text` TEXT,
                `send_column` TEXT
            )";
            if (!mysqli_query($this->connection, $sql)) {
                return "Error creating table: " . mysqli_error($this->connection);
                exit();
            }
        }

        // auto create user_table_views
        if (!$this->check_table_exists('default_table_views')) {
            $sql = "CREATE TABLE IF NOT EXISTS default_table_views (
                record_id INT(255) AUTO_INCREMENT PRIMARY KEY,
                `table_name` TEXT,
                `column_name` TEXT
            )";
            if (!mysqli_query($this->connection, $sql)) {
                return "Error creating table: " . mysqli_error($this->connection);
                exit();
            }
        }



        // auto create user_table_views
        if (!$this->check_table_exists('default_links')) {
            $sql = "CREATE TABLE IF NOT EXISTS default_links (
                record_id INT(255) AUTO_INCREMENT PRIMARY KEY,
                `parent_table_name` TEXT,
                `parent_table_column` TEXT,
                `child_table_name` TEXT,
                `pull_column_name` TEXT,
                  `function` TEXT,
                `display_method` TEXT,
                `onclick` TEXT,
                `onclick_file` TEXT,
                `icon` TEXT,
                `custom_header` TEXT,
                `custom_text` TEXT,
                `send_column` TEXT
              
            )";
            if (!mysqli_query($this->connection, $sql)) {
                return "Error creating table: " . mysqli_error($this->connection);
                exit();
            }
        }


        // auto create user_table_views
        if (!$this->check_table_exists('user_type_app_access')) {
            $sql = "CREATE TABLE IF NOT EXISTS user_type_app_access (
                record_id INT(255) AUTO_INCREMENT PRIMARY KEY,
                `user_type_id` INT(255),
                `file_name` TEXT
            )";
            if (!mysqli_query($this->connection, $sql)) {
                return "Error creating table: " . mysqli_error($this->connection);
                exit();
            }
        }

        // auto create excluded_file_identifiers
        if (!$this->check_table_exists('excluded_file_identifiers')) {
            $sql = "CREATE TABLE IF NOT EXISTS excluded_file_identifiers (
                record_id INT(255) AUTO_INCREMENT PRIMARY KEY,
                `text` TEXT
            )";
            if (!mysqli_query($this->connection, $sql)) {
                return "Error creating table: " . mysqli_error($this->connection);
                exit();
            }


            $sql = "INSERT INTO excluded_file_identifiers (`text`) VALUES ('pdf')";

            if (!mysqli_query($this->connection, $sql)) {
                return "Error inserting first user: " . mysqli_error($this->connection);
                exit();
            }


            $sql = "INSERT INTO excluded_file_identifiers (`text`) VALUES ('edit')";

            if (!mysqli_query($this->connection, $sql)) {
                return "Error inserting first user: " . mysqli_error($this->connection);
                exit();
            }

            $sql = "INSERT INTO excluded_file_identifiers (`text`) VALUES ('save')";

            if (!mysqli_query($this->connection, $sql)) {
                return "Error inserting first user: " . mysqli_error($this->connection);
                exit();
            }


            $sql = "INSERT INTO excluded_file_identifiers (`text`) VALUES ('delete')";

            if (!mysqli_query($this->connection, $sql)) {
                return "Error inserting first user: " . mysqli_error($this->connection);
                exit();
            }



            $sql = "INSERT INTO excluded_file_identifiers (`text`) VALUES ('update')";

            if (!mysqli_query($this->connection, $sql)) {
                return "Error inserting first user: " . mysqli_error($this->connection);
                exit();
            }


        }

        // auto create terms
        if (!$this->check_table_exists('terms')) {
            $sql = "CREATE TABLE IF NOT EXISTS `terms` (
                `record_id` int(255) AUTO_INCREMENT PRIMARY KEY,
                `document_type` varchar(50) NOT NULL,
                `order_type` varchar(100) NOT NULL,
                `terms` text NOT NULL,
                `notes` text NOT NULL DEFAULT ''
            )";
            if (!mysqli_query($this->connection, $sql)) {
                return "Error creating table: " . mysqli_error($this->connection);
                exit();
            }
            $rows = [
                ['quote',   'SUPPLY & INSTALL',  'Light fittings used with PVC should not exceed a 50°C heat deflection. Use LED Lights.
This quotation will be valid for 30 days only.
Upfront payment of 70% compulsory before any material is ordered.
30% after installation is complete.
Custom cut panels and open packages are not refundable.
Please make sure of your sizes.', 'TERMS:
All material stays property of E Designs and Installations (Pty) Ltd until paid in full.	 	 	 	 
EDPVC reserves the right to remove all/any material not paid for by the client.	 	 	 	 
This quotation does not include any electrical work or removing, replacing, or installation of electrical wiring or plugs.
This quotation does not include any electrical wiring work, removing, re-wiring or replacement of any electrical wires	 
Client to employ his own electrician for electrical re-wiring installation.	 	 	 	 
This quotation does not include any Pine Wood Brand ring OR suspended ceiling grit	 	 	 
supply or installation thereof, should it be necessary, usage will be charged additionally to this quotation.	 
EDPVC and Camfly factory will not be held responsible for colour variances on batch number differences.	 	 
     
E Designs gives a 2 year guarantee on the craftsmanship if the installation is done by our professional team.	 	 
SPC Flooring requires a flat surface with no defects for installation on top of a concreet floor. 
Insure screed on floor is sufficient for installation purposes.	 	 	 
SPC Flooring is supplied in boxes of 7 panels. Left over panels will not be refunded.	 	 	 	 
SPC Flooring is scratch RESISTANT - Not scratch PROOF - Please paste filt stickers under furniture which will be moved around on the floor
Cleaning of SVC Floor and PVC Ceiling Panels with soft mop/cloth and warm water ONLY - No harsh chemicals that can damage or discolour panels.'],
                ['quote',   'SUPPLY',            'Light fittings used with PVC should not exceed a 50°C heat deflection. Use LED Lights.
This quotation will be valid for 30 days only.
Upfront payment of 100% compulsory before any material is ordered.
Custom cut panels and open packages are not refundable. please check your sizes.', 'TERMS:
All material stays property of E Designs and Installations (Pty) Ltd until paid in full.	 	 	 	 
EDPVC reserves the right to remove all/any material not paid for by the client.	 	 	 	 
This quotation does not include any electrical work or removing, replacing, or installation of electrical wiring or plugs.
This quotation does not include any electrical wiring work, removing, re-wiring or replacement of any electrical wires	 
Client to employ his own electrician for electrical re-wiring installation.	 	 	 	 
This quotation does not include any Pine Wood Brand ring OR suspended ceiling grit	 	 	 
supply or installation thereof, should it be necessary, usage will be charged additionally to this quotation.	 
EDPVC and Camfly factory will not be held responsible for colour variances on batch number differences.	 	 
     
E Designs gives a 2 year guarantee on the craftsmanship if the installation is done by our professional team.	 	 
SPC Flooring requires a flat surface with no defects for installation on top of a concreet floor. 
Insure screed on floor is sufficient for installation purposes.	 	 	 
SPC Flooring is supplied in boxes of 7 panels. Left over panels will not be refunded.	 	 	 	 
SPC Flooring is scratch RESISTANT - Not scratch PROOF - Please paste filt stickers under furniture which will be moved around on the floor
Cleaning of SVC Floor and PVC Ceiling Panels with soft mop/cloth and warm water ONLY - No harsh chemicals that can damage or discolour panels.'],
                ['quote',   'SUPPLY & DELIVERY', 'Light fittings used with PVC should not exceed a 50°C heat deflection. Use LED Lights.
This quotation will be valid for 30 days only.
Upfront payment of 100% compulsory before any material is ordered.
Custom cut panels and open packages are not refundable. please check your sizes.', 'TERMS:
All material stays property of E Designs and Installations (Pty) Ltd until paid in full.	 	 	 	 
EDPVC reserves the right to remove all/any material not paid for by the client.	 	 	 	 
This quotation does not include any electrical work or removing, replacing, or installation of electrical wiring or plugs.
This quotation does not include any electrical wiring work, removing, re-wiring or replacement of any electrical wires	 
Client to employ his own electrician for electrical re-wiring installation.	 	 	 	 
This quotation does not include any Pine Wood Brand ring OR suspended ceiling grit	 	 	 
supply or installation thereof, should it be necessary, usage will be charged additionally to this quotation.	 
EDPVC and Camfly factory will not be held responsible for colour variances on batch number differences.	 	 
     
E Designs gives a 2 year guarantee on the craftsmanship if the installation is done by our professional team.	 	 
SPC Flooring requires a flat surface with no defects for installation on top of a concreet floor. 
Insure screed on floor is sufficient for installation purposes.	 	 	 
SPC Flooring is supplied in boxes of 7 panels. Left over panels will not be refunded.	 	 	 	 
SPC Flooring is scratch RESISTANT - Not scratch PROOF - Please paste filt stickers under furniture which will be moved around on the floor
Cleaning of SVC Floor and PVC Ceiling Panels with soft mop/cloth and warm water ONLY - No harsh chemicals that can damage or discolour panels.'],
                ['invoice', 'SUPPLY & INSTALL',  'Light fittings used with PVC should not exceed a 50°C heat deflection. Use LED Lights.
This quotation will be valid for 30 days only.
Upfront payment of 70% compulsory before any material is ordered.
30% after installation is complete.
Custom cut panels and open packages are not refundable.
Please make sure of your sizes.', 'THANK YOU FOR THE SUPPORT!
PAYMENT OPTIONS:  EFT Transfer, CREDIT CARD OR CASH'],
                ['invoice', 'SUPPLY',            'Light fittings used with PVC should not exceed a 50°C heat deflection. Use LED Lights.
This quotation will be valid for 30 days only.
Upfront payment of 100% compulsory before any material is ordered.
Custom cut panels and open packages are not refundable. please check your sizes.', 'THANK YOU FOR THE SUPPORT!
PAYMENT OPTIONS:  EFT Transfer, CREDIT CARD OR CASH'],
                ['invoice', 'SUPPLY & DELIVERY', 'Light fittings used with PVC should not exceed a 50°C heat deflection. Use LED Lights.
This quotation will be valid for 30 days only.
Upfront payment of 100% compulsory before any material is ordered.
Custom cut panels and open packages are not refundable. please check your sizes.', 'THANK YOU FOR THE SUPPORT!
PAYMENT OPTIONS:  EFT Transfer, CREDIT CARD OR CASH'],
            ];
            foreach ($rows as $row) {
                $doc = $row[0]; $ot = $row[1]; $t = addslashes($row[2]); $n = addslashes($row[3]);
                mysqli_query($this->connection, "INSERT INTO `terms` (`document_type`, `order_type`, `terms`, `notes`) VALUES ('$doc', '$ot', '$t', '$n')");
            }
        }

        // auto create user_table_views
        if (!$this->check_table_exists('status')) {
            $sql = "CREATE TABLE IF NOT EXISTS `status` (
                record_id INT(255) AUTO_INCREMENT PRIMARY KEY,
                `name` TEXT
              
            )";
            if (!mysqli_query($this->connection, $sql)) {
                return "Error creating table: " . mysqli_error($this->connection);
                exit();
            }


            $sql = "INSERT INTO status (`name`) VALUES ('ACTIVE')";

            if (!mysqli_query($this->connection, $sql)) {
                return "Error inserting first user: " . mysqli_error($this->connection);
                exit();
            }


            $sql = "INSERT INTO status (`name`) VALUES ('INACTIVE')";

            if (!mysqli_query($this->connection, $sql)) {
                return "Error inserting first user: " . mysqli_error($this->connection);
                exit();
            }
        }


    }

    function session_check()
    {
        if (strlen($_SESSION["user_id"]) >= 1) {
            return $_SESSION["user_id"];
        } else {
            return 0;
        }
    }

    /**
     * Logs in a user with the given username and password.
     *
     * @param string $username The username to log in with.
     * @param string $password The password to log in with.
     *
     * @return int 1 if the login was successful, 0 otherwise.
     */
    function login($username, $password)
    {
        $hash_pass = hash("SHA256", $password);
        $sql = "SELECT * FROM users WHERE username = '$username' AND user_password = '$hash_pass'";
        $result = mysqli_query($this->connection, $sql);
        if (mysqli_num_rows($result) > 0) {
            $row = mysqli_fetch_assoc($result);
            $_SESSION["user_id"] = $row["record_id"];
            $_SESSION["user_type"] = $row["user_type"];
            $_SESSION["database_log"] = "hello";

            return 1;
        } else {
            return "0";
        }
    }

    function check_table_exists($table)
    {
        $this->table_name = $table;
        $sql = "SHOW TABLES LIKE '$table' ";
        $result = mysqli_query($this->connection, $sql);

        if (mysqli_num_rows($result) > 0) {

            return true;

        } else {

            return false;

        }
    }

    public function select_query($table_name, $selector, $where_clause)
    {

        $this->sql = "SELECT $selector FROM $table_name WHERE $where_clause";

        if (!$this->check_table_exists($table_name)) {

            echo "[SQL] TABLE NAME DOES NOT EXIST OR IS INCORRECT $table_name";
            echo "[SQL QUERY FAILED]" . mysqli_error($this->connection) . "<br>";
            echo "[SQL QUERY]: " . $this->sql;

        }

        $result = mysqli_query($this->connection, $this->sql);


        if (mysqli_error($this->connection)) {

            echo "[SQL QUERY FAILED]" . mysqli_error($this->connection) . "<br>";
            echo "[SQL QUERY]: " . $this->sql;
            exit();
        } else {

            return $result;

        }
    }

    public function query($table_name, $sql)
    {
        $this->sql = $sql;
        $this->table_name = $table_name;
        if (!$this->check_table_exists($table_name)) {
            echo "[SQL] TABLE NAME DOES NOT EXIST OR IS INCORRECT $table_name";
        }
        $result = mysqli_query($this->connection, $this->sql);

        if (mysqli_error($this->connection)) {
            echo "[SQL QUERY FAILED] on " . $_SERVER['REQUEST_URI'] . " at line " . __LINE__ . ": " . mysqli_error($this->connection) . "<br>";
            echo "[SQL QUERY]: " . $sql;
            exit();
        } else {
            if (stripos(trim($sql), 'INSERT') === 0) {
                return mysqli_insert_id($this->connection);
            } else {
                return $result;
            }
        }
    }

    public function __destruct()
    {

        if (@strlen($this->sql) > 1) {
            if (stripos(trim($this->sql), 'SELECT') !== 0) {
                $log_sql = "INSERT INTO logs (`table_name`,`user_id`,`query`) VALUES (\"$this->table_name\", \"{$_SESSION['user_id']}\", \"$this->sql\")";
                mysqli_query($this->connection, $log_sql);
            }
        }
        mysqli_close($this->connection);
    }
}