class DBQuerys
{
    public static function createConnection($connect = 'mysqli') {
        if ($connect === 'mysqli') {
            return new mysqli(
                DBHOST,
                DBUSER,
                DBPASSWORD,
                DBNAME
            );
        } else {
            return new PDO(
                'mysql:host='.DBHOST.';dbname='.DBNAME,
                DBUSER,
                DBPASSWORD
            );
        }
    }


    public static function insertIntoDB($table = null, $uploadImage){

        $image_base64 = '';
        if (isset($uploadImage['files'][0]['file'])) {
            $image_base64 = base64_encode(file_get_contents($uploadImage['files'][0]['file']));
        }

        if($table === null){
            exit("Tabellen-Name muss angegeben werden");
        }

        $conn = self::createConnection();

        // Check connection
        if ($conn->connect_error) {
            echo "Datenbank-Probelme";
            exit("Datenbank Verbindungsprobleme: " . $conn->connect_error);
        }

        $sql = "SHOW COLUMNS FROM " . $table;
        $query = $conn->query($sql);
        while($row = $query->fetch_assoc()){
            $result[] = $row;
        }

        $sql = "INSERT INTO " . $table . " (";
        $sqlValues = " VALUES ( ";

        foreach ($result as $key => $value){
            if($value['Field'] !== 'ID'){
                $sql .= $value['Field'] . ', ';
                switch($value['Field']){
                    case 'image':
                        $sqlValues .= "'" . $image_base64 . "'" . ', ';
                        break;
                    case 'dateFrom':
                    case'dateTo':
                        $sqlValues .= "'" . date("Y-m-d", strtotime($_POST[$value['Field']])) . "'" . ', ';
                        break;
                    default:
                        $sqlValues .= "'" . utf8_decode($_POST[$value['Field']]) . "'" . ', ';
                        break;
                }
            }
        }


        // *** remove last comma, put closing bracket
        $sql = substr($sql, 0, -2);
        $sql .= ")";
        $sqlValues = substr($sqlValues, 0, -2);
        $sqlValues .= ")";

        $url =  explode( '?', $_SERVER['HTTP_REFERER'] );
        $sql = $sql . $sqlValues;


        if ($conn->query($sql) === TRUE) {
            session_start();
            $_SESSION['info'] = 'Datensatz erfolgreich angelegt';
            header('Location:' . $url[0] ,"r");
        } else {
            echo "Error: Fehler beim Anlegen der Daten! " . $conn->error . $sql;
        }

        $conn->close();
    }


    public static function updateDB ($table = null, $uploadImage){
        if($table === null){
            exit("Tabellen-Name muss angegeben werden");
        }
        $conn = self::createConnection();

        // Check connection
        if ($conn->connect_error) {
            echo "Datenbank-Probelme";
            exit("Datenbank Verbindungsprobleme: " . $conn->connect_error);
        }

        // *** Determine columns of the table
        $sql = "SHOW COLUMNS FROM " . $table;
        $query = $conn->query($sql);
        while($row = $query->fetch_assoc()){
            $result[] = $row;
        }

        // *** without image
        $sql = 'UPDATE events SET ';
        $sqlID = 'WHERE ';
        foreach ($result as $key => $value){
            if($value['Field'] !== 'ID'){
                switch($value['Field']){
                    case 'image':
                        if(empty($uploadImage['files'])){
                            $sql; // do nothing
                        } else {
                            $image_base64 = '';
                            if (isset($uploadImage['files'][0]['file'])) {
                                $image_base64 = base64_encode(file_get_contents($uploadImage['files'][0]['file']));
                            }
                            $sql .= $value['Field']
                                . " = '"
                                . $image_base64 . "'" . ', ';
                        }

                        break;
                    case 'dateFrom':
                    case'dateTo':
                        $sql .= $value['Field']
                            . " = '"
                            . date("Y.m.d", strtotime($_POST[$value['Field']]))
                            . "'" . ', ';
                        break;
                    default:
                        $sql .= $value['Field']
                            . " = '"
                            . utf8_decode($_POST[$value['Field']]) . "'" . ', ';
                        break;
                }
            } else {
                $sqlID .= $value['Field'] . ' = ' . $_POST[$value['Field']];
            }
        }

        // *** remove last comma, put closing bracket
        $sql = substr($sql, 0, -2);
        $sql .= ' ';
        $url =  explode( '?', $_SERVER['HTTP_REFERER'] );
        $sql = $sql . $sqlID;

        session_start();
        if ($conn->query($sql) === TRUE) {
            $_SESSION['info'] = 'Datensatz erfolgreich upgedatet';
            header('Location:' . $url[0],"r");
        } else {
            echo "Error: Fehler beim Anlegen der Daten! " .  $conn->error .  $sql;
        }
        $conn->close();

    }


    public static function deleteData($ID){

        $conn = self::createConnection();

        $sql = "DELETE FROM events WHERE ID = " . $ID;
        $statement = $conn->prepare($sql);
        $statement->execute();
    }
}