<?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_electrosys')
    {
        $this->connection = mysqli_connect($host, $user, $password, $dbname);

        // 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 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);
    }
}