Merge pull request #76 from PhoenixTwoFive/PhoenixTwoFive-patch-1

Update devcontainer.json

.devcontainer/devcontainer.json

@@ -0,0 +1,40 @@
+{
+  "image": "mcr.microsoft.com/devcontainers/universal:2",
+  "hostRequirements": {
+    "cpus": 4
+  },
+  "waitFor": "onCreateCommand",
+  "onCreateCommand": "curl -LsS https://r.mariadb.com/downloads/mariadb_repo_setup | sudo bash && sudo apt install -y libmariadb3 libmariadb-dev",
+  "updateContentCommand": "pip install -r backend/requirements.txt",
+  "portsAttributes": {
+    "5000": {
+      "label": "Application",
+      "onAutoForward": "openPreview"
+    }
+  },
+  "customizations": {
+    "codespaces": {
+      "openFiles": [
+        "templates/index.html"
+      ]
+    },
+    "vscode": {
+      "extensions": [
+		"ms-python.python",
+		"batisteo.vscode-django",
+		"ms-python.flake8",
+		"ms-python.isort",
+		"ms-python.vscode-pylance",
+		"redhat.vscode-yaml",
+		"ms-azuretools.vscode-docker",
+		"donjayamanne.python-extension-pack"
+	]
+    }
+  },
+"forwardPorts": [
+  5000
+],
+"features": {
+  "ghcr.io/devcontainers-contrib/features/angular-cli:2": {}
+}
+}

.editorconfig

@@ -0,0 +1,36 @@
+# EditorConfig is awesome: https://EditorConfig.org
+
+# top-most EditorConfig file
+root = true
+
+[*]
+indent_style = space
+indent_size = 4
+end_of_line = lf
+charset = utf-8
+trim_trailing_whitespace = false
+insert_final_newline = false
+
+[*.md]
+trim_trailing_whitespace = true
+
+[*.py]
+indent_size = 4
+
+[*.js]
+indent_size = 2
+
+[*.html]
+indent_size = 2
+
+[*.css]
+indent_size = 2
+
+[*.scss]
+indent_size = 2
+
+[*.yaml]
+indent_size = 2
+
+[*.yml]
+indent_size = 2

.env.dev

@@ -7,8 +7,6 @@ MARIADB_PASSWORD=mariadb_karaoqueue_password
 
 # Karaoqueue
 DEPLOYMENT_PLATFORM=Docker
-DBSTRING=mysql://karaoqueue:mariadb_karaoqueue_password@127.0.0.1:3306/karaoqueue
-BASIC_AUTH_USERNAME=admin 
-BASIC_AUTH_PASSWORD=change_me
-ENTRY_QUOTA=3
-MAX_QUEUE=20
+DBSTRING="mysql+pymysql://karaoqueue:mariadb_karaoqueue_password@127.0.0.1:3306/karaoqueue?charset=utf8mb4"
+INITIAL_USERNAME=admin
+INITIAL_PASSWORD=changeme

.flake8

@@ -0,0 +1,3 @@
+[flake8]
+ignore = E501
+max-line-length = 120

.github/workflows/lint.yaml

@@ -0,0 +1,23 @@
+name: Lint
+
+on: [pull_request]
+
+jobs:
+  flake8_py3:
+    runs-on: ubuntu-latest
+    steps:
+      - name: Setup Python
+        uses: actions/setup-python@v4.6.0
+        with:
+          python-version: '3.10'
+          architecture: x64
+      - name: Checkout PyTorch
+        uses: actions/checkout@master
+      - name: Install flake8
+        run: pip install flake8
+      - name: Run flake8
+        uses: suo/flake8-github-action@releases/v1
+        with:
+          checkName: 'flake8_py3'   # NOTE: this needs to be the same as the job name
+        env:
+          GITHUB_TOKEN: ${{ secrets.GITHUB_TOKEN }}

.gitignore

@@ -87,7 +87,7 @@ ipython_config.py
 #   However, in case of collaboration, if having platform-specific dependencies or dependencies
 #   having no cross-platform support, pipenv may install dependencies that don't work, or not
 #   install all needed dependencies.
-#Pipfile.lock
+Pipfile.lock
 
 # celery beat schedule file
 celerybeat-schedule

.vscode/settings.json

@@ -1,3 +1,17 @@
 {
-    "python.pythonPath": "/usr/bin/python"
+    "python.pythonPath": "/usr/bin/python",
+    "python.testing.unittestArgs": [
+        "-v",
+        "-s",
+        "./backend/tests",
+        "-p",
+        "*test.py"
+    ],
+    "python.testing.pytestEnabled": false,
+    "python.testing.unittestEnabled": true,
+    "python.linting.pylintEnabled": false,
+    "python.linting.flake8Enabled": true,
+    "emmet.includeLanguages": {
+        "django-html": "html"
+    }
 }

Dockerfile

@@ -1,5 +1,23 @@
-FROM tiangolo/uwsgi-nginx-flask:python3.7
+FROM tiangolo/meinheld-gunicorn-flask:python3.9
 
-COPY ./backend /app
+# Currently unusable, mariadb is not available through installer for debian 12
+# RUN apt-key adv --recv-keys --keyserver keyserver.ubuntu.com 0xcbcb082a1bb943db
+# RUN curl -LsS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | bash
 
-RUN pip install -r /app/requirements.txt
+RUN apt-get update
+RUN apt-get upgrade -y
+RUN apt-get dist-upgrade
+
+# In the meantime, acquire the mariadb packages through apt
+RUN apt-get install -y libmariadb3 libmariadb-dev
+
+COPY ./backend/requirements.txt /app/requirements.txt
+
+RUN pip install --no-cache-dir --upgrade -r /app/requirements.txt
+
+RUN pip install --no-cache-dir -U meinheld
+
+ARG SOURCE_VERSION
+ENV SOURCE_VERSION ${SOURCE_VERSION:-unknown}
+
+COPY ./backend /app

backend/Pipfile

@@ -0,0 +1,48 @@
+[[source]]
+url = "https://pypi.org/simple"
+verify_ssl = true
+name = "pypi"
+
+[packages]
+autopep8 = "==2.0.2"
+beautifulsoup4 = "==4.12.0"
+bs4 = "==0.0.1"
+certifi = "==2022.12.7"
+charset-normalizer = "==3.1.0"
+click = "==8.1.3"
+flake8 = "==6.0.0"
+flask = "==2.2.3"
+flask-basicauth = "==0.2.0"
+greenlet = "==2.0.2"
+gunicorn = "==20.1.0"
+idna = "==3.4"
+itsdangerous = "==2.1.2"
+jinja2 = "==3.1.2"
+mariadb = "==1.1.6"
+markupsafe = "==2.1.2"
+mccabe = "==0.7.0"
+mysql = "==0.0.3"
+mysqlclient = "==2.1.1"
+numpy = "==1.24.2"
+packaging = "==23.0"
+pandas = "==1.5.3"
+pycodestyle = "==2.10.0"
+pyflakes = "==3.0.1"
+pymysql = "==1.0.3"
+python-dateutil = "==2.8.2"
+pytz = "==2023.3"
+requests = "==2.28.2"
+six = "==1.16.0"
+soupsieve = "==2.4"
+sqlalchemy = "==2.0.7"
+toml = "==0.10.2"
+tomli = "==2.0.1"
+typing-extensions = "==4.5.0"
+urllib3 = "==1.26.15"
+werkzeug = "==2.2.3"
+
+[dev-packages]
+
+[requires]
+python_version = "3.9"
+python_full_version = "3.9.17"

backend/app.py

@@ -1,5 +1,5 @@
 from flask import Flask, render_template, abort, request, redirect, send_from_directory, jsonify
-from flask.wrappers import Request, Response
+from flask.wrappers import Response
 import helpers
 import database
 import data_adapters
@@ -12,12 +12,13 @@ app = Flask(__name__, static_url_path='/static')
 basic_auth = BasicAuth(app)
 accept_entries = True
 
+
 @app.route("/")
 def home():
     if basic_auth.authenticate():
-        return render_template('main_admin.html', list=database.get_list(), auth=basic_auth.authenticate())
+        return render_template('main_admin.html', list=database.get_list(), auth=basic_auth.authenticate(), debug=app.config['DEBUG'])
     else:
-        return render_template('main.html', list=database.get_list(), auth=basic_auth.authenticate())
+        return render_template('main.html', list=database.get_list(), auth=basic_auth.authenticate(), debug=app.config['DEBUG'])
 
 
 @app.route("/favicon.ico")
@@ -36,13 +37,13 @@ def enqueue():
     if not helpers.is_valid_uuid(client_id):
         print(request.data)
         abort(400)
-    name = request.json['name']
+    name = request.json['name'].strip()
     song_id = request.json['id']
     if request.authorization:
-        database.add_entry(name, song_id, client_id)
-        return Response('{"status":"OK"}', mimetype='text/json')
+        entry_id = database.add_entry(name, song_id, client_id)
+        return Response(f"""{{"status":"OK", "entry_id":{entry_id}}}""", mimetype='text/json')
     else:
-        if accept_entries:
+        if helpers.get_accept_entries(app):
             if not request.json:
                 print(request.data)
                 abort(400)
@@ -54,8 +55,8 @@ def enqueue():
             song_id = request.json['id']
             if database.check_queue_length() < int(app.config['MAX_QUEUE']):
                 if database.check_entry_quota(client_id) < int(app.config['ENTRY_QUOTA']):
-                    database.add_entry(name, song_id, client_id)
-                    return Response('{"status":"OK"}', mimetype='text/json')
+                    entry_id = database.add_entry(name, song_id, client_id)
+                    return Response(f"""{{"status":"OK", "entry_id":{entry_id}}}""", mimetype='text/json')
                 else:
                     return Response('{"status":"Du hast bereits ' + str(database.check_entry_quota(client_id)) + ' Songs eingetragen, dies ist das Maximum an Einträgen die du in der Warteliste haben kannst."}', mimetype='text/json', status=423)
             else:
@@ -66,14 +67,14 @@ def enqueue():
 
 @app.route("/list")
 def songlist():
-    return render_template('songlist.html', list=database.get_song_list(), auth=basic_auth.authenticate())
+    return render_template('songlist.html', list=database.get_song_list(), auth=basic_auth.authenticate(), debug=app.config['DEBUG'])
 
 
 @app.route("/settings")
 @nocache
 @basic_auth.required
 def settings():
-    return render_template('settings.html', app=app, auth=basic_auth.authenticate())
+    return render_template('settings.html', app=app, auth=basic_auth.authenticate(), themes=helpers.get_themes(), debug=app.config['DEBUG'])
 
 
 @app.route("/settings", methods=['POST'])
@@ -82,6 +83,10 @@ def settings():
 def settings_post():
     entryquota = request.form.get("entryquota")
     maxqueue = request.form.get("maxqueue")
+    theme = request.form.get("theme")
+    username = request.form.get("username")
+    password = request.form.get("password")
+    changed_credentials = False
     if entryquota.isnumeric() and int(entryquota) > 0:  # type: ignore
         app.config['ENTRY_QUOTA'] = int(entryquota)  # type: ignore
     else:
@@ -90,8 +95,21 @@ def settings_post():
         app.config['MAX_QUEUE'] = int(maxqueue)  # type: ignore
     else:
         abort(400)
-
-    return render_template('settings.html', app=app, auth=basic_auth.authenticate())
+    if theme is not None and theme in helpers.get_themes():
+        helpers.set_theme(app, theme)
+    else:
+        abort(400)
+    if username != "" and username != app.config['BASIC_AUTH_USERNAME']:
+        app.config['BASIC_AUTH_USERNAME'] = username
+        changed_credentials = True
+    if password != "":
+        app.config['BASIC_AUTH_PASSWORD'] = password
+        changed_credentials = True
+    helpers.persist_config(app=app)
+    if changed_credentials:
+        return redirect("/")
+    else:
+        return render_template('settings.html', app=app, auth=basic_auth.authenticate(), themes=helpers.get_themes(), debug=app.config['DEBUG'])
 
 
 @app.route("/api/queue")
@@ -105,7 +123,7 @@ def queue_json():
 @nocache
 @basic_auth.required
 def played_list():
-    return render_template('played_list.html', list=database.get_played_list(), auth=basic_auth.authenticate())
+    return render_template('played_list.html', list=database.get_played_list(), auth=basic_auth.authenticate(), debug=app.config['DEBUG'])
 
 
 @app.route("/api/songs")
@@ -120,18 +138,18 @@ def songs():
 @basic_auth.required
 def update_songs():
     database.delete_all_entries()
+    helpers.reset_current_event_id(app)
     status = database.import_songs(
         helpers.get_songs(helpers.get_catalog_url()))
     print(status)
     return Response('{"status": "%s" }' % status, mimetype='text/json')
 
 
-@app.route("/api/songs/compl")
+@app.route("/api/songs/compl")  # type: ignore
 @nocache
 def get_song_completions(input_string=""):
     input_string = request.args.get('search', input_string)
     if input_string != "":
-        print(input_string)
         result = [list(x) for x in database.get_song_completions(input_string=input_string)]
         return jsonify(result)
 
@@ -139,10 +157,51 @@ def get_song_completions(input_string=""):
         return 400
 
 
-@app.route("/api/entries/delete/<entry_id>")
+@app.route("/api/songs/search")
+@nocache
+def query_songs_with_details(input_string=""):
+    input_string = request.args.get("q", input_string)
+    if input_string == "":
+        return Response(status=400)
+    result = []
+    for x in database.get_songs_with_details(input_string):
+        # Turn row into dict. Add field labels.
+        result.append(dict(zip(['karafun_id', 'title', 'artist', 'year', 'duo', 'explicit', 'styles', 'languages'], x)))
+    return jsonify(result)
+
+
+@app.route("/api/songs/details/<song_id>")
+def get_song_details(song_id):
+    result = database.get_song_details(song_id)
+    if result is None:
+        abort(404)
+    else:
+        return jsonify(dict(zip(['karafun_id', 'title', 'artist', 'year', 'duo', 'explicit', 'styles', 'languages'], result[0])))
+
+
+@app.route("/api/entries/delete/<entry_id>", methods=['GET'])
 @nocache
 @basic_auth.required
-def delete_entry(entry_id):
+def delete_entry_admin(entry_id):
+    if database.delete_entry(entry_id):
+        return Response('{"status": "OK"}', mimetype='text/json')
+    else:
+        return Response('{"status": "FAIL"}', mimetype='text/json')
+
+
+@app.route("/api/entries/delete/<entry_id>", methods=['POST'])
+@nocache
+def delete_entry_user(entry_id):
+    if not request.json:
+        print(request.data)
+        abort(400)
+    client_id = request.json['client_id']
+    if not helpers.is_valid_uuid(client_id):
+        print(request.data)
+        abort(400)
+    if database.get_raw_entry(entry_id)[3] != client_id:  # type: ignore
+        print(request.data)
+        abort(403)
     if database.delete_entry(entry_id):
         return Response('{"status": "OK"}', mimetype='text/json')
     else:
@@ -159,7 +218,7 @@ def delete_entries():
         return
     updates = database.delete_entries(request.json)
     if updates >= 0:
-        return Response('{"status": "OK", "updates": '+str(updates)+'}', mimetype='text/json')
+        return Response('{"status": "OK", "updates": ' + str(updates) + '}', mimetype='text/json')
     else:
         return Response('{"status": "FAIL"}', mimetype='text/json', status=400)
 
@@ -173,6 +232,7 @@ def mark_sung(entry_id):
     else:
         return Response('{"status": "FAIL"}', mimetype='text/json')
 
+
 @app.route("/api/entries/mark_transferred/<entry_id>")
 @nocache
 @basic_auth.required
@@ -187,9 +247,8 @@ def mark_transferred(entry_id):
 @nocache
 @basic_auth.required
 def set_accept_entries(value):
-    global accept_entries
     if (value == '0' or value == '1'):
-        accept_entries = bool(int(value))
+        helpers.set_accept_entries(app, bool(int(value)))
         return Response('{"status": "OK"}', mimetype='text/json')
     else:
         return Response('{"status": "FAIL"}', mimetype='text/json', status=400)
@@ -198,8 +257,8 @@ def set_accept_entries(value):
 @app.route("/api/entries/accept")
 @nocache
 def get_accept_entries():
-    global accept_entries
-    return Response('{"status": "OK", "value": '+str(int(accept_entries))+'}', mimetype='text/json')
+    accept_entries = helpers.get_accept_entries(app)
+    return Response('{"status": "OK", "value": ' + str(int(accept_entries)) + '}', mimetype='text/json')
 
 
 @app.route("/api/played/clear")
@@ -217,6 +276,7 @@ def clear_played_songs():
 @basic_auth.required
 def delete_all_entries():
     if database.delete_all_entries():
+        helpers.reset_current_event_id(app)
         return Response('{"status": "OK"}', mimetype='text/json')
     else:
         return Response('{"status": "FAIL"}', mimetype='text/json')
@@ -228,19 +288,24 @@ def admin():
     return redirect("/", code=303)
 
 
-@app.before_first_request
-def activate_job():
-    helpers.load_dbconfig(app)
-    helpers.load_version(app)
-    helpers.create_data_directory()
-    database.create_entry_table()
-    database.create_song_table()
-    database.create_done_song_table()
-    database.create_list_view()
-    database.create_done_song_view()
-    helpers.setup_config(app)
+@app.route("/api/events/current")
+@nocache
+def get_current_event():
+    return Response('{"status": "OK", "event": "' + helpers.get_current_event_id(app) + '"}', mimetype='text/json')
 
 
+def activate_job():
+    with app.app_context():
+        helpers.load_dbconfig(app)
+        helpers.load_version(app)
+        database.create_entry_table()
+        database.create_song_table()
+        database.create_done_song_table()
+        database.create_list_view()
+        database.create_done_song_view()
+        database.create_config_table()
+        helpers.setup_config(app)
+
 
 @app.after_request
 def add_header(response):
@@ -248,14 +313,15 @@ def add_header(response):
     Add headers to both force latest IE rendering engine or Chrome Frame,
     and also to cache the rendered page for 10 minutes.
     """
-    if not 'Cache-Control' in response.headers:
-        response.headers['Cache-Control'] = 'private, max-age=600'
+    if 'Cache-Control' not in response.headers:
+        response.headers['Cache-Control'] = 'private, max-age=600, no-cache, must-revalidate'
     return response
 
+
 @app.context_processor
 def inject_version():
     return dict(karaoqueue_version=app.config['VERSION'])
 
 
-if __name__ == "__main__":
-    app.run(host='127.0.0.1', port=8080, debug=True)
+# Perform setup here so it will be executed when the module is imported by the WSGI server.
+activate_job()

backend/data_adapters.py

@@ -1,8 +1,5 @@
-def dict_from_row(row):
-    return dict(zip(row.keys(), row))
-
 def dict_from_rows(rows):
-    outlist=[]
+    outlist = []
     for row in rows:
-        outlist.append(dict_from_row(row))
-    return outlist
+        outlist.append(dict(row._mapping))
+    return outlist

backend/database.py

@@ -1,11 +1,10 @@
 # -*- coding: utf_8 -*-
 
-from email.mime import base
-from MySQLdb import Connection
-from sqlalchemy import create_engine, engine
+from sqlalchemy import create_engine, engine, text
 import pandas
 from io import StringIO
 from flask import current_app
+import uuid
 
 song_table = "songs"
 entry_table = "entries"
@@ -18,8 +17,8 @@ sql_engine = None
 def get_db_engine() -> engine.base.Engine:
     global sql_engine
     if (not sql_engine):
-        print(current_app.config.get("DBCONNSTRING"))
-        sql_engine = create_engine(current_app.config.get("DBCONNSTRING"))  # type: ignore
+        sql_engine = create_engine(
+            current_app.config.get("DBCONNSTRING"))  # type: ignore
     return sql_engine
 
 
@@ -28,139 +27,235 @@ def import_songs(song_csv):
     df = pandas.read_csv(StringIO(song_csv), sep=';')
     with get_db_engine().connect() as conn:
         df.to_sql(song_table, conn, if_exists='replace',
-                index=False)
-        cur = conn.execute("SELECT Count(Id) FROM songs")
-        num_songs = cur.fetchone()[0] # type: ignore
+                  index=False)
+        try:
+            cur = conn.execute(text("ALTER TABLE songs ADD FULLTEXT(Title,Artist)"))
+            conn.commit()
+        except Exception:
+            pass
+        cur = conn.execute(text("SELECT Count(Id) FROM songs"))
+        num_songs = cur.fetchone()[0]  # type: ignore
+        conn.commit()
     print("Imported songs ({} in Database)".format(num_songs))
-    return("Imported songs ({} in Database)".format(num_songs))
+    return ("Imported songs ({} in Database)".format(num_songs))
 
 
 def create_entry_table():
     with get_db_engine().connect() as conn:
-        conn.execute('CREATE TABLE IF NOT EXISTS '+entry_table +
-                 ' (ID INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT, Song_Id INTEGER NOT NULL, Name VARCHAR(255), Client_Id VARCHAR(36), Transferred INTEGER DEFAULT 0)')
+        stmt = text(
+            f'CREATE TABLE IF NOT EXISTS `{entry_table}` (ID INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT, Song_Id INTEGER NOT NULL, Name VARCHAR(255), Client_Id VARCHAR(36), Transferred INTEGER DEFAULT 0)')
+        conn.execute(stmt)
+        conn.commit()
 
 
 def create_done_song_table():
     with get_db_engine().connect() as conn:
-        conn.execute('CREATE TABLE IF NOT EXISTS '+done_table +
-                 ' (Song_Id INTEGER PRIMARY KEY NOT NULL,  Plays INTEGER)')
+        stmt = text(
+            f'CREATE TABLE IF NOT EXISTS `{done_table}` (Song_Id INTEGER PRIMARY KEY NOT NULL,  Plays INTEGER)')
+        conn.execute(stmt)
+        conn.commit()
 
 
 def create_song_table():
     with get_db_engine().connect() as conn:
-        conn.execute("CREATE TABLE IF NOT EXISTS `"+song_table+"""` (
+        stmt = text(f"""CREATE TABLE IF NOT EXISTS `{song_table}` (
         `Id` INTEGER,
         `Title` TEXT,
         `Artist` TEXT,
-        `Year` INTEGER,
-        `Duo` INTEGER,
+        `Year` VARCHAR(4),
+        `Duo` BOOLEAN,
         `Explicit` INTEGER,
-        `Date Added` TEXT,
+        `Date Added` TIMESTAMP,
         `Styles` TEXT,
-        `Languages` TEXT
-    )""")
+        `Languages` TEXT,
+        PRIMARY KEY (`Id`),
+        FULLTEXT KEY (`Title`,`Artist`)
+        )""")
+        conn.execute(stmt)
+        conn.commit()
 
 
 def create_list_view():
     with get_db_engine().connect() as conn:
-        conn.execute("""CREATE OR REPLACE VIEW `Liste` AS
+        stmt = text("""CREATE OR REPLACE VIEW `Liste` AS
                  SELECT Name, Title, Artist, entries.Id AS entry_ID, songs.Id AS song_ID, entries.Transferred
                  FROM entries, songs
-                 WHERE entries.Song_Id=songs.Id""")
+                 WHERE entries.Song_Id=songs.Id
+                 ORDER BY entries.Id ASC
+                 """)
+        conn.execute(stmt)
+        conn.commit()
 
 
 def create_done_song_view():
     with get_db_engine().connect() as conn:
-        conn.execute("""CREATE OR REPLACE VIEW `Abspielliste` AS
+        stmt = text("""CREATE OR REPLACE VIEW `Abspielliste` AS
                  SELECT CONCAT(Artist," - ", Title) AS Song, Plays AS Wiedergaben
                  FROM songs, done_songs
                  WHERE done_songs.Song_Id=songs.Id""")
+        conn.execute(stmt)
+        conn.commit()
+
+
+def create_config_table():
+    with get_db_engine().connect() as conn:
+        stmt = text("""CREATE TABLE IF NOT EXISTS `config` (
+        `Key` VARCHAR(50) NOT NULL PRIMARY KEY,
+        `Value` TEXT
+        )""")
+        conn.execute(stmt)
+        conn.commit()
 
 
 def get_list():
     with get_db_engine().connect() as conn:
-        cur = conn.execute("SELECT * FROM Liste")
+        stmt = text("SELECT * FROM Liste")
+        cur = conn.execute(stmt)
     return cur.fetchall()
 
 
 def get_played_list():
     with get_db_engine().connect() as conn:
-        cur = conn.execute("SELECT * FROM Abspielliste")
+        stmt = text("SELECT * FROM Abspielliste")
+        cur = conn.execute(stmt)
     return cur.fetchall()
 
 
 def get_song_list():
     with get_db_engine().connect() as conn:
-        cur = conn.execute(
-        "SELECT Artist || \" - \" || Title AS Song, Id FROM songs;")
+        stmt = text("SELECT Artist || \" - \" || Title AS Song, Id FROM songs;")
+        cur = conn.execute(stmt)
     return cur.fetchall()
 
 
 def get_song_completions(input_string):
     with get_db_engine().connect() as conn:
-        # Don't look, it burns...
-        prepared_string = "%{0}%".format(
-            input_string).upper()  # "Test" -> "%TEST%"
-        print(prepared_string)
+        prepared_string = f"{input_string}"
+        prepared_string_with_wildcard = f"%{input_string}%"
+        stmt = text(
+            """
+            SELECT CONCAT(Artist, ' - ', Title) AS Song, Id FROM songs
+            WHERE MATCH(Artist, Title)
+            AGAINST (:prepared_string IN NATURAL LANGUAGE MODE)
+            LIMIT 20;
+            """)
         cur = conn.execute(
-            "SELECT CONCAT(Artist,\" - \",Title) AS Song, Id FROM songs WHERE CONCAT(Artist,\" - \",Title) LIKE (%s) LIMIT 20;", [prepared_string])
-    return cur.fetchall()
+            stmt, {"prepared_string": prepared_string, "prepared_string_with_wildcard": prepared_string_with_wildcard})  # type: ignore
+        return cur.fetchall()
+
+
+def get_songs_with_details(input_string: str):
+    with get_db_engine().connect() as conn:
+        prepared_string = f"%{input_string}"
+        stmt = text(
+            """
+            SELECT Id, Title, Artist, Year, Duo, Explicit, Styles, Languages FROM songs
+            WHERE MATCH(Artist, Title)
+            AGAINST (:prepared_string IN NATURAL LANGUAGE MODE)
+            LIMIT 20;
+            """
+        )
+        cur = conn.execute(
+            stmt, {"prepared_string": prepared_string})
+        return cur.fetchall()
+
+
+def get_song_details(song_id: int):
+    with get_db_engine().connect() as conn:
+        stmt = text(
+            """
+            SELECT Id, Title, Artist, Year, Duo, Explicit, Styles, Languages FROM songs
+            WHERE Id = :song_id;
+            """
+        )
+        cur = conn.execute(
+            stmt, {"song_id": song_id})
+        return cur.fetchall()
 
 
 def add_entry(name, song_id, client_id):
     with get_db_engine().connect() as conn:
-        conn.execute(
-        "INSERT INTO entries (Song_Id,Name,Client_Id) VALUES(%s,%s,%s);", (song_id, name, client_id))
-    return
+        stmt = text(
+            "INSERT INTO entries (Song_Id,Name,Client_Id) VALUES (:par_song_id,:par_name,:par_client_id) RETURNING entries.ID;")
+        cur = conn.execute(stmt, {"par_song_id": song_id, "par_name": name,
+                                  "par_client_id": client_id})  # type: ignore
+        conn.commit()
+    return cur.fetchone()[0]  # type: ignore
 
 
 def add_sung_song(entry_id):
     with get_db_engine().connect() as conn:
-        cur = conn.execute(
-            """SELECT Song_Id FROM entries WHERE Id=%s""", (entry_id,))
-        song_id = cur.fetchone()[0] # type: ignore
-        conn.execute("""INSERT INTO done_songs (Song_Id, Plays) VALUES("""+str(song_id)+""",1) ON DUPLICATE KEY UPDATE Plays=Plays + 1;""")
+        stmt = text("SELECT Song_Id FROM entries WHERE Id=:par_entry_id")
+        cur = conn.execute(stmt, {"par_entry_id": entry_id})  # type: ignore
+        song_id = cur.fetchone()[0]  # type: ignore
+        stmt = text(
+            "INSERT INTO done_songs (Song_Id,Plays) VALUES (:par_song_id,1) ON DUPLICATE KEY UPDATE Plays=Plays + 1;")
+        conn.execute(stmt, {"par_song_id": song_id})  # type: ignore
+        conn.commit()
         delete_entry(entry_id)
     return True
 
 
 def toggle_transferred(entry_id):
     with get_db_engine().connect() as conn:
-        cur = conn.execute(
-            "SELECT Transferred FROM entries WHERE ID =%s", (entry_id,))
+        cur = conn.execute(text("SELECT Transferred FROM entries WHERE ID = :par_entry_id"),
+                           {"par_entry_id": entry_id})  # type: ignore
         marked = cur.fetchall()[0][0]
-        if(marked == 0):
-            conn.execute(
-                "UPDATE entries SET Transferred = 1 WHERE ID =%s", (entry_id,))
+        if (marked == 0):
+            conn.execute(text("UPDATE entries SET Transferred = 1 WHERE ID = :par_entry_id"),
+                         {"par_entry_id": entry_id})  # type: ignore
         else:
-            conn.execute(
-                "UPDATE entries SET Transferred = 0 WHERE ID =%s", (entry_id,))
+            conn.execute(text("UPDATE entries SET Transferred = 0 WHERE ID = :par_entry_id"),
+                         {"par_entry_id": entry_id})  # type: ignore
+        conn.commit()
     return True
 
 
 def check_entry_quota(client_id):
     with get_db_engine().connect() as conn:
-        cur = conn.execute(
-            "SELECT Count(*) FROM entries WHERE entries.Client_Id = %s", (client_id,))
+        cur = conn.execute(text("SELECT Count(*) FROM entries WHERE entries.Client_Id = :par_client_id"),
+                           {"par_client_id": client_id})  # type: ignore
     return cur.fetchall()[0][0]
 
 
 def check_queue_length():
     with get_db_engine().connect() as conn:
-        cur = conn.execute("SELECT Count(*) FROM entries")
+        cur = conn.execute(text("SELECT Count(*) FROM entries"))
     return cur.fetchall()[0][0]
 
 
 def clear_played_songs():
     with get_db_engine().connect() as conn:
-        conn.execute("DELETE FROM done_songs")
+        conn.execute(text("DELETE FROM done_songs"))
+        conn.commit()
     return True
 
 
+def get_entry(id):
+    try:
+        with get_db_engine().connect() as conn:
+            cur = conn.execute(text("SELECT * FROM Liste WHERE entry_ID = :par_id"),
+                               {"par_id": id})  # type: ignore
+        return cur.fetchall()[0]
+    except Exception:
+        return None
+
+
+def get_raw_entry(id):
+    try:
+        with get_db_engine().connect() as conn:
+            cur = conn.execute(text("SELECT * FROM entries WHERE ID = :par_id"),
+                               {"par_id": id})  # type: ignore
+        return cur.fetchall()[0]
+    except Exception:
+        return None
+
+
 def delete_entry(id):
     with get_db_engine().connect() as conn:
-        conn.execute("DELETE FROM entries WHERE id=%s", (id,))
+        conn.execute(text("DELETE FROM entries WHERE id= :par_id"), {
+                     "par_id": id})  # type: ignore
+        conn.commit()
     return True
 
 
@@ -170,14 +265,74 @@ def delete_entries(ids):
         idlist.append((x,))
     try:
         with get_db_engine().connect() as conn:
-            cur = conn.execute("DELETE FROM entries WHERE id=%s", idlist)
-
+            cur = conn.execute(text("DELETE FROM entries WHERE id= :par_id"), {
+                               "par_id": idlist})
+            conn.commit()
         return cur.rowcount
-    except Exception as error:
+    except Exception:
         return -1
 
 
-def delete_all_entries():
+def delete_all_entries() -> bool:
     with get_db_engine().connect() as conn:
-        conn.execute("DELETE FROM entries")
+        conn.execute(text("DELETE FROM entries"))
+        conn.commit()
     return True
+
+
+def get_config(key: str) -> str:
+    try:
+        with get_db_engine().connect() as conn:
+            cur = conn.execute(
+                text("SELECT `Value` FROM config WHERE `Key`= :par_key"), {"par_key": key})  # type: ignore
+            conn.commit()
+        return cur.fetchall()[0][0]
+    except IndexError:
+        return ""
+
+
+def set_config(key: str, value: str) -> bool:
+    with get_db_engine().connect() as conn:
+        conn.execute(text(
+            "INSERT INTO config (`Key`, `Value`) VALUES ( :par_key , :par_value) ON DUPLICATE KEY UPDATE `Value`= :par_value"),
+            {"par_key": key, "par_value": value}
+        )  # type: ignore
+        conn.commit()
+    return True
+
+
+def get_config_list() -> dict:
+    with get_db_engine().connect() as conn:
+        cur = conn.execute(text("SELECT * FROM config"))
+        result_dict = {}
+        for row in cur.fetchall():
+            result_dict[row[0]] = row[1]
+    return result_dict
+
+
+def check_config_table() -> bool:
+    with get_db_engine().connect() as conn:
+        if conn.dialect.has_table(conn, 'config'):
+            # type: ignore
+            # type: ignore
+            if (conn.execute(text("SELECT COUNT(*) FROM config")).fetchone()[0] > 0):  # type: ignore
+                return True
+            else:
+                return False
+        else:
+            return False
+
+
+def init_event_id() -> bool:
+    if not get_config("EventID"):
+        set_config("EventID", str(uuid.uuid4()))
+    return True
+
+
+def reset_event_id() -> bool:
+    set_config("EventID", str(uuid.uuid4()))
+    return True
+
+
+def get_event_id() -> str:
+    return get_config("EventID")

backend/helpers.py

@@ -1,30 +1,27 @@
 import requests
 from bs4 import BeautifulSoup
-import json
+import subprocess
 import os
 import uuid
-from flask import make_response
+from flask import make_response, Flask
 from functools import wraps, update_wrapper
 from datetime import datetime
-
-data_directory = "data"
-config_file = data_directory+"/config.json"
-
-def create_data_directory():
-    if not os.path.exists(data_directory):
-        os.makedirs(data_directory)
+import database
 
 
 def get_catalog_url():
     r = requests.get('https://www.karafun.de/karaoke-song-list.html')
     soup = BeautifulSoup(r.content, 'html.parser')
-    url = soup.findAll('a', href=True, text='Verfügbar in CSV-Format')[0]['href']
+    url = soup.findAll(
+        'a', href=True, text='Verfügbar in CSV-Format')[0]['href']
     return url
 
+
 def get_songs(url):
     r = requests.get(url)
     return r.text
 
+
 def is_valid_uuid(val):
     try:
         uuid.UUID(str(val))
@@ -32,36 +29,36 @@ def is_valid_uuid(val):
     except ValueError:
         return False
 
-def check_config_exists():
-    return os.path.isfile(config_file)
 
-def load_version(app):
+def check_config_exists():
+    return database.check_config_table()
+
+
+def load_version(app: Flask):
+    if app.config['DEBUG'] is True:
+        app.config['VERSION'] = subprocess.Popen("echo \"$(git rev-parse --abbrev-ref HEAD)-$(git describe)\"", shell=True, stdout=subprocess.PIPE).stdout.read().decode('utf-8').strip() + " (debug)"  # noqa: E501 # type: ignore
+        return
     if os.environ.get("SOURCE_VERSION"):
-        app.config['VERSION'] = os.environ.get("SOURCE_VERSION")[0:7] # type: ignore
-    elif os.path.isfile(".version"):
-        with open('.version', 'r') as file:
-            data = file.read().replace('\n', '')
-            if data:
-                app.config['VERSION'] = data
-            else:
-                app.config['VERSION'] = ""
+        app.config['VERSION'] = os.environ.get("SOURCE_VERSION")  # type: ignore # noqa: E501
+        return
     else:
-        app.config['VERSION'] = ""
-        
-def load_dbconfig(app):
+        app.config['VERSION'] = "Unknown"
+
+
+def load_dbconfig(app: Flask):
     if os.environ.get("FLASK_ENV") == "development":
         app.config['DBCONNSTRING'] = os.environ.get("DBSTRING")
     else:
-        if os.environ.get("DEPLOYMENT_PLATFORM") == "Heroku":    
+        if os.environ.get("DEPLOYMENT_PLATFORM") == "Heroku":
             if os.environ.get("JAWSDB_MARIA_URL"):
                 app.config['DBCONNSTRING'] = os.environ.get("JAWSDB_MARIA_URL")
             else:
                 app.config['DBCONNSTRING'] = ""
-        if os.environ.get("DEPLOYMENT_PLATFORM") == "Docker": 
+        if os.environ.get("DEPLOYMENT_PLATFORM") == "Docker":
             if os.environ.get("DBSTRING"):
                 app.config['DBCONNSTRING'] = os.environ.get("DBSTRING")
             else:
-                app.config['DBCONNSTRING'] = ""   
+                app.config['DBCONNSTRING'] = ""
         elif os.path.isfile(".dbconn"):
             with open('.dbconn', 'r') as file:
                 data = file.read().replace('\n', '')
@@ -70,30 +67,99 @@ def load_dbconfig(app):
                 else:
                     app.config['DBCONNSTRING'] = ""
         else:
-            app.config['DBCONNSTRING'] = ""
+            exit("""No database connection string found. Cannot continue.
+                 Please set the environment variable DBSTRING or
+                 create a file .dbconn in the root directory of the project.""")
 
-def setup_config(app):
-    if os.environ.get("DEPLOYMENT_PLATFORM") == "Docker":
-        app.config['BASIC_AUTH_USERNAME'] = os.environ.get('BASIC_AUTH_USERNAME')
-        app.config['BASIC_AUTH_PASSWORD'] = os.environ.get('BASIC_AUTH_PASSWORD')
-        app.config['ENTRY_QUOTA'] = os.environ.get('ENTRY_QUOTA')
-        app.config['MAX_QUEUE'] = os.environ.get('MAX_QUEUE')
+# Check if config exists in DB, if not, create it.
+
+
+def setup_config(app: Flask):
+    if check_config_exists() is False:
+        print("No config found, creating new config")
+        initial_username = os.environ.get("INITIAL_USERNAME")
+        initial_password = os.environ.get("INITIAL_PASSWORD")
+        if initial_username is None:
+            print(
+                "No initial username set. Please set the environment variable INITIAL_USERNAME")
+            exit()
+        if initial_password is None:
+            print(
+                "No initial password set. Please set the environment variable INITIAL_PASSWORD")
+            exit()
+        default_config = {'username': initial_username,
+                          'password': initial_password,
+                          'entryquota': 2,
+                          'maxqueue': 10,
+                          'entries_allowed': 1,
+                          'theme': 'default.css'}
+        for key, value in default_config.items():
+            database.set_config(key, value)
+        print("Created new config")
+        database.init_event_id()
+    config = database.get_config_list()
+    app.config['BASIC_AUTH_USERNAME'] = config['username']
+    app.config['BASIC_AUTH_PASSWORD'] = config['password']
+    app.config['ENTRY_QUOTA'] = config['entryquota']
+    app.config['MAX_QUEUE'] = config['maxqueue']
+    app.config['ENTRIES_ALLOWED'] = bool(config['entries_allowed'])
+    app.config['THEME'] = config['theme']
+    app.config['EVENT_ID'] = database.get_event_id()
+
+# set queue admittance
+
+
+def set_accept_entries(app: Flask, allowed: bool):
+    if allowed:
+        app.config['ENTRIES_ALLOWED'] = True
+        database.set_config('entries_allowed', '1')
     else:
-        if check_config_exists():
-            config = json.load(open(config_file))
-            with open(config_file, 'r') as handle:
-                config = json.load(handle)
-            print("Loaded existing config")
-        else:
-            config = {'username': 'admin', 'password': 'changeme', 'entryquota': 3, 'maxqueue': 20}
-            with open(config_file, 'w') as handle:
-                json.dump(config, handle, indent=4, sort_keys=True)
-            print("Wrote new config")
-        app.config['BASIC_AUTH_USERNAME'] = config['username']
-        app.config['BASIC_AUTH_PASSWORD'] = config['password']
-        app.config['ENTRY_QUOTA'] = config['entryquota']
-        app.config['MAX_QUEUE'] = config['maxqueue']
+        app.config['ENTRIES_ALLOWED'] = False
+        database.set_config('entries_allowed', '0')
 
+# get queue admittance
+
+
+def get_accept_entries(app: Flask) -> bool:
+    state = bool(int(database.get_config('entries_allowed')))
+    app.config['ENTRIES_ALLOWED'] = state
+    return state
+
+
+# Write settings from current app.config to DB
+def persist_config(app: Flask):
+    config = {'username': app.config['BASIC_AUTH_USERNAME'], 'password': app.config['BASIC_AUTH_PASSWORD'],
+              'entryquota': app.config['ENTRY_QUOTA'], 'maxqueue': app.config['MAX_QUEUE']}
+    for key, value in config.items():
+        database.set_config(key, value)
+
+# Get available themes from themes directory
+
+
+def get_themes():
+    themes = []
+    for theme in os.listdir('./static/css/themes'):
+        themes.append(theme)
+    return themes
+
+# Set theme
+
+
+def set_theme(app: Flask, theme: str):
+    if theme in get_themes():
+        app.config['THEME'] = theme
+        database.set_config('theme', theme)
+    else:
+        print("Theme not found, not setting theme.")
+
+
+def get_current_event_id(app: Flask):
+    return app.config['EVENT_ID']
+
+
+def reset_current_event_id(app: Flask):
+    database.reset_event_id()
+    app.config['EVENT_ID'] = database.get_event_id()
 
 
 def nocache(view):
@@ -105,5 +171,5 @@ def nocache(view):
         response.headers['Pragma'] = 'no-cache'
         response.headers['Expires'] = '-1'
         return response
-        
-    return update_wrapper(no_cache, view)
+
+    return update_wrapper(no_cache, view)

backend/main.py

@@ -1,4 +1,4 @@
 from app import app
 
 if __name__ == "__main__":
-        app.run()
+    app.run()

backend/requirements.txt

@@ -1,7 +1,36 @@
-requests
-pandas
-Flask-BasicAuth
-bs4
-gunicorn
-SQLAlchemy
-mysqlclient
+autopep8~=2.0.2
+beautifulsoup4~=4.12.0
+bs4~=0.0.1
+certifi~=2022.12.7
+charset-normalizer~=3.1.0
+click~=8.1.3
+flake8~=6.0.0
+Flask~=2.3.2
+Flask-BasicAuth~=0.2.0
+greenlet~=2.0.2
+gunicorn~=20.1.0
+idna~=3.4
+itsdangerous~=2.1.2
+Jinja2~=3.1.2
+mariadb~=1.1.6
+MarkupSafe~=2.1.2
+mccabe~=0.7.0
+mysql~=0.0.3
+mysqlclient~=2.1.1
+numpy~=1.24.2
+packaging~=23.0
+pandas~=1.5.3
+pycodestyle~=2.10.0
+pyflakes~=3.0.1
+PyMySQL~=1.0.3
+python-dateutil~=2.8.2
+pytz~=2023.3
+requests~=2.31.0
+six~=1.16.0
+soupsieve~=2.4
+SQLAlchemy~=2.0.7
+toml~=0.10.2
+tomli~=2.0.1
+typing_extensions~=4.5.0
+urllib3~=1.26.15
+Werkzeug~=3.0.0

backend/static/css/style.css

@@ -1,77 +1,238 @@
-body {
-    padding-top: 5rem;
+:root {
+  /* Navbar */
+  --navbar-background-color: #343a40;
+  --navbar-text-color: rgba(255, 255, 255, .5);
+  --navbar-text-color-hover: rgba(255, 255, 255, .75);
+  --navbar-text-color-active: rgba(255, 255, 255, 1);
+  --navbar-padding: 4.5rem;
+
+  /* Common */
+  --background-color: #ffffff;
+  --background-color-var: #f5f5f5;
+  --text-color: #212529;
+  --text-color-var: #343a40;
+  --text-color-light: #6c757d;
+
+  /* Modals */
+  --modal-background-color: #ffffff;
+  --modal-separator-color: #dee2e6;
+  --modal-close-color: #212529;
+
+  /* Tables */
+  --table-border-color: #dee2e6;
+
+  /* Input */
+  --input-background-color: #ffffff;
+
+  /* Misc */
+  --copy-highlight-color: rgba(251, 255, 0, 0.6);
+
+  /* Toasts */
+  --toast-background-color: #ffffff;
+  --toast-text-color: #212529;
+
+  /* Footer */
+  --footer-height: 80px;
 }
 
-html, body {
-    height: 100%;
+
+body {
+  padding-top: var(--navbar-padding);
+  background-color: var(--background-color);
+}
+
+html,
+body {
+  height: 100%;
 }
 
 .site {
-    height: auto;
-    min-height: 100%;
-}
-
-main {
-    padding-bottom: 60px; /* Höhe des Footers */
+  min-height: calc(100vh - var(--navbar-padding) - var(--footer-height));
+  margin-bottom: -var(--footer-height);
 }
 
 .footer {
-    margin-top: -60px;
-    width: 100%;
-    height: 60px;
-    /* Set the fixed height of the footer here */
-    /*line-height: 60px; /* Vertically center the text there */
-    background-color: #f5f5f5;
+  /*margin-top: var(--footer-height);*/
+  width: 100%;
+  height: var(--footer-height);
+
+  background-color: var(--background-color-var);
 }
 
 .topbutton {
-    width: 100%;
+  width: 100%;
 }
 
 table td {
-    overflow: hidden;
-    text-overflow: ellipsis;
+  overflow: hidden;
+  text-overflow: ellipsis;
 }
 
 table.entries tbody tr[data-index="0"] {
-    background-color: #007bff80;
-    font-weight: 600;
+  background-color: #007bff80;
+  font-weight: 600;
 }
 
 table.entries tbody tr[data-index="1"] {
-    background-color: #007bff40;
-    font-weight: 500;
+  background-color: #007bff40;
+  font-weight: 500;
 }
 
 table.entries tbody tr[data-index="2"] {
-    background-color: #007bff20;
-    font-weight: 400;
+  background-color: #007bff20;
+  font-weight: 400;
 }
 
 table.entries tbody tr[data-index="3"] {
-    background-color: #007bff10;
+  background-color: #007bff10;
 }
 
 table td:first-child {
-    max-width: 200px !important;
+  max-width: 200px !important;
+}
+
+.fa-solid {
+  vertical-align: auto;
 }
 
 @media (min-width: 768px) {
-    .topbutton {
-        width: auto;
-    }
+  .topbutton {
+    width: auto;
+  }
 }
 
 @media print {
-    body {
-        font-size: 1.3em;
-    }
+  body {
+    font-size: 1.3em;
+  }
 
-    .footer {
-        display: none !important;
-    }
+  .footer {
+    display: none !important;
+  }
 
-    .admincontrols {
-        display: none;
-    }
+  .admincontrols {
+    display: none;
+  }
+}
+
+body {
+  background-color: var(--background-color);
+  color: var(--text-color);
+}
+
+.footer {
+  background-color: var(--background-color-var);
+}
+
+.modal-content {
+  background-color: var(--background-color);
+  color: var(--text-color);
+}
+
+.modal-header {
+  background-color: var(--background-color);
+  color: var(--text-color-var);
+  border-color: var(--modal-separator-color);
+}
+
+.modal-footer {
+  background-color: var(--background-color);
+  color: var(--text-color-var);
+  border-color: var(--modal-separator-color);
+}
+
+.form-control {
+  background-color: var(--input-background-color);
+  color: var(--text-color)
+}
+
+.form-control:focus {
+  background-color: var(--input-background-color);
+  color: var(--text-color)
+}
+
+.table td,
+.table th {
+  border-color: var(--table-border-color)
+}
+
+.table thead th {
+  border-color: var(--table-border-color)
+}
+
+table td.buttoncell {
+  text-align: end;
+}
+
+.close {
+  color: var(--text-color)
+}
+
+pre {
+  color: var(--text-color-var)
+}
+
+#entrytable td>span:hover {
+  background-color: var(--copy-highlight-color);
+}
+
+.list-indicator {
+  width: 1.5rem;
+  height: 1.5rem;
+}
+
+#songYear {
+  font-size: small;
+  font-weight: 400;
+  color: var(--text-color-light);
+}
+
+.construction_bg {
+  background: repeating-linear-gradient(45deg,
+      #222200,
+      #222200 10px,
+      #000000 10px,
+      #000000 20px) !important;
+}
+
+.toast {
+  background-color: var(--toast-background-color);
+  color: var(--toast-text-color);
+}
+
+.toast-header {
+  background-color: var(--toast-background-color);
+  color: var(--toast-text-color);
+}
+
+@media (prefers-color-scheme: dark) {
+  :root {
+    /* Navbar */
+    --navbar-background-color: #343a40;
+    --navbar-text-color: rgba(255, 255, 255, .5);
+    --navbar-text-color-hover: rgba(255, 255, 255, .75);
+    --navbar-text-color-active: rgba(255, 255, 255, 1);
+
+    /* Common */
+    --background-color: #121212;
+    --background-color-var: #232323;
+    --text-color: #f5f5f5;
+    --text-color-var: #a2a2a2;
+    --text-color-light: #6c757d;
+
+    /* Modals */
+    --modal-background-color: #121212;
+    --modal-separator-color: #232323;
+    --modal-close-color: #f5f5f5;
+
+    /* Tables */
+    --table-border-color: #232323;
+
+    /* Input */
+    --input-background-color: #343434;
+
+    /* Toasts */
+    --toast-background-color: #232323;
+    --toast-text-color: #f5f5f5;
+  }
 }

backend/static/css/themes/stuk.css

@@ -0,0 +1,91 @@
+.navbar {
+    background: #090a28 !important;
+}
+
+.navbar .navbar-toggle:hover,
+.navbar .navbar-toggle:focus {
+    background-color: #900000 !important;
+}
+
+.navbar .navbar-toggle {
+    border: none;
+}
+
+.navbar .navbar-nav>.open>a,
+.navbar .navbar-nav>.open>a:hover,
+.navbar .navbar-nav>.open>a:focus {
+    color: #CF2323 !important;
+    background-color: #050515 !important;
+}
+
+.btn-primary {
+    background-color: #15175b;
+    border-color: #15175b;
+}
+
+.btn-primary:hover,
+.btn-primary:focus {
+    background-color: #0e103e;
+    background-position: 0 -15px;
+}
+
+.btn-primary:active,
+.btn-primary.active {
+    background-color: #0e103e;
+    border-color: #0e103e;
+}
+
+.btn-primary.disabled,
+.btn-primary:disabled,
+.btn-primary[disabled] {
+    background-color: #0e103e;
+    background-image: none;
+}
+
+.dropdown-menu>li>a:hover,
+.dropdown-menu>li>a:focus {
+    color: #8A0711;
+}
+
+.dropdown-menu>.active>a,
+.dropdown-menu>.active>a:hover,
+.dropdown-menu>.active>a:focus {
+    background-color: #2e6da4;
+    background-image: -webkit-linear-gradient(top, #337ab7 0%, #2e6da4 100%);
+    background-image: -o-linear-gradient(top, #337ab7 0%, #2e6da4 100%);
+    background-image: -webkit-gradient(linear, left top, left bottom, from(#337ab7), to(#2e6da4));
+    background-image: linear-gradient(to bottom, #337ab7 0%, #2e6da4 100%);
+    filter: progid:DXImageTransform.Microsoft.gradient(startColorstr='#ff337ab7', endColorstr='#ff2e6da4', GradientType=0);
+    background-repeat: repeat-x;
+}
+
+.navbar .navbar-nav>li>a:hover {
+    color: #b60000;
+}
+
+.form-control:focus {
+    border-color: rgba(21, 23, 91, 0.8);
+    outline: 0;
+    -webkit-box-shadow: inset 0 1px 1px rgba(0, 0, 0, .075),
+    0 0 8px rgba(21, 23, 91, 0.6);
+    box-shadow: 0 1px 1px rgba(0, 0, 0, 0.075) inset,
+    0 0 8px rgba(21, 23, 91, 0.6);
+}
+
+a {
+    color: #900000;
+}
+
+a:hover,
+a:focus {
+    color: #670000;
+}
+
+.navbar-brand {
+    display: flex;
+    align-items: center;
+}
+
+.navbar-brand > img {
+    height: 4rem;
+}

backend/templates/base.html

@@ -6,6 +6,7 @@
     <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
     <meta name="description" content="">
     <meta name="author" content="">
+    <meta name="color-scheme" content="light dark" />
     <link rel="icon" href="favicon.ico">
     <link rel="manifest" href="/static/manifest.webmanifest">
 
@@ -14,20 +15,30 @@
     <!-- Bootstrap-Tables -->
     <link rel="stylesheet" href="https://unpkg.com/bootstrap-table@1.21.2/dist/bootstrap-table.min.css">
 
+    <!-- Bootstrap-Toaster-->
+    <link rel="stylesheet"
+        href="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-toaster/4.1.2/css/bootstrap-toaster.min.css"
+        integrity="sha512-kYPLvO+Bu+xttOhbQvxs9nx7XSdxrb2JexRxQ3CpJQ7EtmlkBsWyOjlinLgiLWeLxuupFYB4cPqLOo0gnBnzeQ=="
+        crossorigin="anonymous" referrerpolicy="no-referrer" />
+
     <!-- Bootstrap core CSS -->
-    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/css/bootstrap.min.css"
-        integrity="sha384-MCw98/SFnGE8fJT3GXwEOngsV7Zt27NXFoaoApmYm81iuXoPkFOJwJ8ERdknLPMO" crossorigin="anonymous">
+    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4.2.1/dist/css/bootstrap.min.css"
+        integrity="sha384-GJzZqFGwb1QTTN6wy59ffF1BuGJpLSa9DkKMp0DgiMDm4iYMj70gZWKYbI706tWS" crossorigin="anonymous">
 
     <!-- Custom styles for this template -->
     <link href="static/css/style.css" rel="stylesheet">
 
     <!-- Fontawesome Icons -->
-    <link rel="stylesheet" href="https://use.fontawesome.com/releases/v5.8.2/css/all.css"
-        integrity="sha384-oS3vJWv+0UjzBfQzYUhtDYW+Pj2yciDJxpsK1OYPAYjqT085Qq/1cq5FLXAZQ7Ay" crossorigin="anonymous">
+    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.4.0/css/all.min.css"
+        integrity="sha512-iecdLmaskl7CVkqkXNQ/ZH/XLlvWZOJyj7Yy7tcenmpD1ypASozpmT/E0iPtmFIB46ZmdtAc9eNBvH0H/ZpiBw=="
+        crossorigin="anonymous" referrerpolicy="no-referrer" />
 
     <!-- Bootstraptoggle -->
     <link href="https://gitcdn.github.io/bootstrap-toggle/2.2.2/css/bootstrap-toggle.min.css" rel="stylesheet">
 
+    <!-- Active Theme -->
+    <link href="static/css/themes/{{config['THEME']}}" rel="stylesheet">
+
 </head>
 
 <body>
@@ -68,13 +79,13 @@
         </main><!-- /.container -->
     </div>
     <!-- Footer -->
-    <footer class="footer">
+    <footer class="footer {% if debug %} construction_bg {% endif %}">
         <div class="container text-center py-3">
             {% if not auth %}
             <a href="/login" class="ml-1 mr-1"><i class="fas fa-sign-in-alt mr-1"></i><span>Login</span></a>
             {% endif %}
-            <a href="https://github.com/PhoenixTwoFive/karaoqueue"
-                class="ml-1 mr-1"><i class="fab fa-github mr-1"></i><span>Github</span></a>
+            <a href="https://github.com/PhoenixTwoFive/karaoqueue" class="ml-1 mr-1"><i
+                    class="fab fa-github mr-1"></i><span>Github</span></a>
             <span class="text-muted"> {{karaoqueue_version}} -&nbsp;2019-23 - Phillip
                 Kühne</span>
         </div>
@@ -83,16 +94,16 @@
     <!-- Bootstrap core JavaScript
     ================================================== -->
     <!-- Placed at the end of the document so the pages load faster -->
-    <script src="https://code.jquery.com/jquery-3.2.1.min.js"
-        integrity="sha384-xBuQ/xzmlsLoJpyjoggmTEz8OWUFM0/RC5BsqQBDX2v5cMvDHcMakNTNrHIW2I5f" crossorigin="anonymous">
-        </script>
+    <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.6.4/jquery.min.js"
+        integrity="sha512-pumBsjNRGGqkPzKHndZMaAG+bir374sORyzM3uulLV14lN5LyykqNk8eEeUlUkB3U0M4FApyaHraT65ihJhDpQ=="
+        crossorigin="anonymous" referrerpolicy="no-referrer"></script>
 
-    <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.3/umd/popper.min.js"
-        integrity="sha384-ZMP7rVo3mIykV+2+9J3UJ46jBk0WLaUAdn689aCwoqbBJiSnjAK/l8WvCWPIPm49" crossorigin="anonymous">
-        </script>
-    <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/js/bootstrap.min.js"
-        integrity="sha384-ChfqqxuZUCnJSK3+MXmPNIyE6ZbWh2IMqE241rYiqJxyMiZ6OW/JmZQ5stwEULTy" crossorigin="anonymous">
-        </script>
+    <script src="https://cdn.jsdelivr.net/npm/popper.js@1.14.6/dist/umd/popper.min.js"
+        integrity="sha384-wHAiFfRlMFy6i5SRaxvfOCifBUQy1xHdJ/yoi7FRNXMRBu5WHdZYu1hA6ZOblgut"
+        crossorigin="anonymous"></script>
+    <script src="https://cdn.jsdelivr.net/npm/bootstrap@4.2.1/dist/js/bootstrap.min.js"
+        integrity="sha384-B0UglyR+jN6CkvvICOB2joaf5I4l3gm9GU6Hc1og6Ls7i6U/mkkaduKaBhlAXv9k"
+        crossorigin="anonymous"></script>
     <script src="https://cdnjs.cloudflare.com/ajax/libs/bootbox.js/4.4.0/bootbox.min.js"
         integrity="sha256-4F7e4JsAJyLUdpP7Q8Sah866jCOhv72zU5E8lIRER4w=" crossorigin="anonymous">
         </script>
@@ -100,10 +111,14 @@
     <script
         src="https://unpkg.com/bootstrap-table@1.21.2/dist/extensions/auto-refresh/bootstrap-table-auto-refresh.min.js"></script>
     <script src="https://gitcdn.github.io/bootstrap-toggle/2.2.2/js/bootstrap-toggle.min.js"></script>
+
+    <script src="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-toaster/4.1.2/js/bootstrap-toaster.min.js"
+        integrity="sha512-Ur6jgeoP3jnn38C7oBzDqMLRb+wxG2PXLKqgx2vgQ1ePFvbJ28f9iQSJplHD0APFHELOeS/df+RPNeENFtLrYw=="
+        crossorigin="anonymous" referrerpolicy="no-referrer"></script>
     {% block extrajs %}{% endblock %}
     <script>
         $(document).ready(function () {
-            loadOrGenerateClientId()
+            checkEventID()
             // get current URL path and assign 'active' class
             var pathname = window.location.pathname;
             $('.navbar-nav > li > a[href="' + pathname + '"]').parent().addClass('active');
@@ -122,9 +137,53 @@
 
         function loadOrGenerateClientId() {
             if (!localStorage.getItem("clientId")) {
-                localStorage.setItem("clientId",create_UUID())
+                localStorage.setItem("clientId", create_UUID())
             }
         }
+
+        function getClientId() {
+            return localStorage.getItem("clientId")
+        }
+
+        async function checkEventID() {
+            const localEventID = localStorage.getItem("eventID")
+            const resp = await fetch("/api/events/current")
+            const respJson = await resp.json()
+            const remoteEventID = respJson.event
+
+            if (localEventID == null || localEventID != remoteEventID) {
+                localStorage.clear()
+                localStorage.setItem("eventID", remoteEventID)
+                loadOrGenerateClientId()
+            }
+        }
+
+        function addEntry(entryId) {
+            entryArray = JSON.parse(localStorage.getItem("ownedEntries"))
+            if (entryArray == null) {
+                entryArray = []
+            }
+            entryArray.push(entryId)
+            localStorage.setItem("ownedEntries", JSON.stringify(entryArray))
+        }
+
+        function removeEntry(entryId) {
+            entryArray = JSON.parse(localStorage.getItem("ownedEntries"))
+            if (entryArray == null) {
+                entryArray = []
+            }
+            entryArray = entryArray.filter(function (value, index, arr) { return value != entryId; });
+            localStorage.setItem("ownedEntries", JSON.stringify(entryArray))
+        }
+
+        function getOwnedEntries() {
+            var entries = JSON.parse(localStorage.getItem("ownedEntries"))
+            if (entries == null) {
+                entries = []
+            }
+            return entries;
+        }
+
     </script>
 </body>
 

backend/templates/main.html

@@ -1,5 +1,3 @@
-
-
 {% extends 'base.html' %}
 {% block title %}Warteliste{% endblock %}
 {% block content %}
@@ -9,7 +7,7 @@
         data-url="/api/queue"
         data-pagination="true"
         data-classes="table"
-        data-show-refresh="false"
+    data-show-refresh="false"
         data-auto-refresh="true"
         data-auto-refresh-interval="10">
     <thead>
@@ -17,6 +15,7 @@
             <th data-field="Name">Name</th>
             <th data-field="Title">Song</th>
             <th data-field="Artist">Künstler</th>
+            <th scope="col" data-formatter="TableActionsFormatter"></th>
         </tr>
     </thead>
 </table>
@@ -24,12 +23,53 @@
 {% endblock %}
 {% block extrajs %}
 <script>
-$.getJSON("/api/entries/accept", (data) => {
-    if (data["value"]==0) {
-        $("#bfb").addClass("disabled")
-        $("#bfb").prop("aria-disabled",true);
-        $("#bfb").prop("tabindex","-1");
+    $.getJSON("/api/entries/accept", (data) => {
+        if (data["value"] == 0) {
+            $("#bfb").addClass("disabled")
+            $("#bfb").prop("aria-disabled", true);
+            $("#bfb").prop("tabindex", "-1");
+            $("#bfb").wrap("<span class='tooltip-span' tabindex='0' data-toggle='tooltip' data-placement='bottom'></span>");
+            $(".tooltip-span").prop("title", "Eintragungen sind leider momentan nicht möglich.")
+            $('[data-toggle="tooltip"]').tooltip()
+        }
+    })
+
+    function TableActionsFormatter(value, row, index) {
+        console.log("Value: " + value + ", Row: " + row + ", Index: " + index)
+        console.log(row)
+        if (getOwnedEntries().includes(row.entry_ID)) {
+            return "<button type='button' class='btn btn-danger' data-toggle='tooltip' data-placement='top' title='Eintrag zurückziehen' onclick=\"event.stopPropagation();$(this).tooltip('dispose');requestDeletionAsUser(" + row["entry_ID"] + ")\"><i class='fas fa-trash'></i></button>"
+        }
+        return ""
     }
-})
+
+    function requestDeletionAsUser(id) {
+        bootbox.confirm("Wirklich den Eintrag zurückziehen? Das könnte zu einer langen Wartezeit führen!", function (result) {
+            if (result) {
+                payload = {
+                    "client_id": localStorage.getItem("clientId"),
+                    "entry_id": id
+                }
+                $.ajax({
+                    url: "/api/entries/delete/" + id,
+                    type: "POST",
+                    data: JSON.stringify(payload),
+                    contentType: "application/json; charset=utf-8",
+                    dataType: "json",
+                    success: function (result) {
+                        toast = {
+                            title: "Erfolgreich zurückgezogen",
+                            message: "Eintrag wurde gelöscht",
+                            status: TOAST_STATUS.SUCCESS,
+                            timeout: 5000
+                        }
+                        Toast.create(toast);
+                        location.reload()
+                    }
+                })
+            }
+        })
+    }
+
 </script>
 {% endblock %}

backend/templates/main_admin.html

@@ -1,15 +1,13 @@
-
-
 {% extends 'base.html' %}
 {% block title %}Warteliste-Admin{% endblock %}
 {% block content %}
 <style>
-table td:nth-child(2) {
-    overflow-y: hidden;
-    overflow-x: auto;
-    text-overflow: clip;
-    max-width: 200px !important;
-}
+    table td:nth-child(2) {
+        overflow-y: hidden;
+        overflow-x: auto;
+        text-overflow: clip;
+        max-width: 200px !important;
+    }
 </style>
 <div class="container">
     <div id="toolbar">
@@ -18,30 +16,19 @@ table td:nth-child(2) {
         <button type="button" class="topbutton btn btn-danger" onclick="confirmUpdateSongDatabase()"><i
                 class="fas fa-file-import mr-2"></i>Song-Datenbank
             aktualisieren</button>
-        <input id="entryToggle" type="checkbox" class="topbutton" data-toggle="toggle" data-on="Eintragen erlaubt" data-off="Eintragen deaktiviert" data-onstyle="success" data-offstyle="danger">
+        <input id="entryToggle" type="checkbox" class="topbutton" data-toggle="toggle" data-on="Eintragen erlaubt"
+            data-off="Eintragen deaktiviert" data-onstyle="success" data-offstyle="danger">
     </div>
-    <table class="table entries"
-            id="entrytable"
-            data-toggle="table"
-            data-search="true"
-            data-show-columns="true"
-            data-show-toggle="true"
-            data-multiple-select-row="true"
-            data-click-to-select="true"
-            data-toolbar="#toolbar"
-            data-pagination="true"
-            data-show-extended-pagination="true"
-            data-classes="table table-hover"
-            data-url="/api/queue"
-            data-show-refresh="true"
-            data-auto-refresh="true"
-            data-auto-refresh-interval="10">
+    <table class="table entries" id="entrytable" data-toggle="table" data-search="true" data-show-columns="true"
+        data-show-toggle="true" data-multiple-select-row="true" data-click-to-select="true" data-toolbar="#toolbar"
+        data-pagination="true" data-show-extended-pagination="true" data-classes="table table-hover"
+        data-url="/api/queue" data-show-refresh="true" data-auto-refresh="true" data-auto-refresh-interval="10">
         <thead>
             <tr>
                 <th data-field="state" data-checkbox="true"></th>
-                <th scope="col" data-field="Name">Name</th>
-                <th scope="col" data-field="Title">Song</th>
-                <th scope="col" data-field="Artist">Künstler</th>
+                <th scope="col" data-field="Name" data-formatter="CopyFormatter">Name</th>
+                <th scope="col" data-field="Title" data-formatter="CopyFormatter">Song</th>
+                <th scope="col" data-field="Artist" data-formatter="CopyFormatter">Künstler</th>
                 <th scope="col" data-formatter="TableActions">Aktionen</th>
             </tr>
         </thead>
@@ -53,15 +40,18 @@ table td:nth-child(2) {
 <script>
     $(function () {
         refreshEntryToggle()
-        $('#entryToggle').change(function() {
-            $.ajax({url: "/api/entries/accept/"+($('#entryToggle').is(":checked") ? "1" : "0"), complete: setTimeout(refreshEntryToggle, 1000)});
+        $('#entryToggle').change(function () {
+            $.ajax({ url: "/api/entries/accept/" + ($('#entryToggle').is(":checked") ? "1" : "0"), complete: setTimeout(refreshEntryToggle, 1000) });
         })
-        $("#entrytable").bootstrapTable().on('load-success.bs.table', function() {
+        $("#entrytable").bootstrapTable().on('load-success.bs.table', function () {
             $('[data-toggle="tooltip"]').tooltip()
         })
+        $('[data-toggle="tooltip"]').tooltip({
+            trigger: 'hover'
+        })
     })
     function confirmDeleteEntry(name, entry_id) {
-        bootbox.confirm("Wirklich den Eintrag von "+name+" löschen?", function(result){
+        bootbox.confirm("Wirklich den Eintrag von " + name + " löschen?", function (result) {
             if (result) {
                 deleteEntry(entry_id)
             }
@@ -69,7 +59,7 @@ table td:nth-child(2) {
     }
     function confirmDeleteSelectedEntries() {
         bootbox.confirm({
-            message: "Wirklich gewählte Eintragungen löschen?", 
+            message: "Wirklich gewählte Eintragungen löschen?",
             buttons: {
                 confirm: {
                     label: 'Ja',
@@ -80,7 +70,7 @@ table td:nth-child(2) {
                     className: 'btn btn-secondary'
                 }
             },
-            callback: function(result){
+            callback: function (result) {
                 if (result) {
                     DeleteSelectedEntries(getIdSelections())
                 }
@@ -100,22 +90,22 @@ table td:nth-child(2) {
                     className: 'btn btn-secondary'
                 }
             },
-            callback: function(result){
+            callback: function (result) {
                 if (result) {
                     var dialog = bootbox.dialog({
                         message: '<p class="text-center mb-0"><i class="fa fa-spin fa-cog"></i> Aktualisiere Song-Datenbank...</p>',
-    
+
                         closeButton: false
                     });
-                    updateSongDatabase(dialog)                    
+                    updateSongDatabase(dialog)
                 }
             }
         })
     }
     function refreshEntryToggle() {
         $.getJSON("/api/entries/accept", (data) => {
-            if (data["value"]!=$('#entryToggle').is(":checked")) {
-                if(data["value"]==1) {
+            if (data["value"] != $('#entryToggle').is(":checked")) {
+                if (data["value"] == 1) {
                     $('#entryToggle').data('bs.toggle').on('true')
                 }
                 else {
@@ -125,53 +115,53 @@ table td:nth-child(2) {
         })
     }
     function deleteEntry(entry_id) {
-       $.ajax({
-        type: 'GET',
-        url: '/api/entries/delete/'+entry_id,
-        contentType: "application/json",
-        dataType: 'json',
-        async: false
-       });
-       $("#entrytable").bootstrapTable('refresh')
-    
+        $.ajax({
+            type: 'GET',
+            url: '/api/entries/delete/' + entry_id,
+            contentType: "application/json",
+            dataType: 'json',
+            async: false
+        });
+        $("#entrytable").bootstrapTable('refresh')
+
     }
     function markEntryAsSung(entry_id) {
-       $.ajax({
-        type: 'GET',
-        url: '/api/entries/mark_sung/'+entry_id,
-        contentType: "application/json",
-        dataType: 'json',
-        async: false
-       });
-       $("#entrytable").bootstrapTable('refresh')
-    
+        $.ajax({
+            type: 'GET',
+            url: '/api/entries/mark_sung/' + entry_id,
+            contentType: "application/json",
+            dataType: 'json',
+            async: false
+        });
+        $("#entrytable").bootstrapTable('refresh')
+
     }
 
     function markEntryAsTransferred(entry_id) {
         $.ajax({
-         type: 'GET',
-         url: '/api/entries/mark_transferred/'+entry_id,
-         contentType: "application/json",
-         dataType: 'json',
-         async: false
+            type: 'GET',
+            url: '/api/entries/mark_transferred/' + entry_id,
+            contentType: "application/json",
+            dataType: 'json',
+            async: false
         });
         $("#entrytable").bootstrapTable('refresh')
-     
-     }
+
+    }
 
     function DeleteSelectedEntries(ids) {
         $.ajax({
             type: 'POST',
             url: '/api/entries/delete',
             data: JSON.stringify(ids), // or JSON.stringify ({name: 'jonas'}),
-            error: function() {
+            error: function () {
                 bootbox.alert({
                     message: "Fehler beim Löschen der Eintragungen.",
                 })
             },
-            success: function() {
+            success: function () {
                 $("#entrytable").bootstrapTable('refresh')
-            
+
             },
             contentType: "application/json",
             dataType: 'json'
@@ -183,32 +173,48 @@ table td:nth-child(2) {
             url: '/api/songs/update',
             contentType: "application/json",
             dataType: 'json',
-            success: function(data) {
+            success: function (data) {
                 wait_dialog.modal('hide')
                 bootbox.alert({
                     message: data["status"],
-                    callback: function() {
+                    callback: function () {
                         $("#entrytable").bootstrapTable('refresh')
                     }
                 })
             }
         });
     }
-    function TableActions (value, row, index) {
-        console.log("Value: "+value+", Row: "+row+", Index: "+index)
-        console.log(row)
+    function TableActions(value, row, index) {
         let outerHTML = ""
-        if (row.Transferred==1) {
-            outerHTML = "<button type=\"button\" class=\"btn btn-default\" onclick=\"markEntryAsTransferred("+row.entry_ID+")\"><i class=\"fas fa-backward\"></i></button>&nbsp;<button type=\"button\" class=\"btn btn-success\" data-toggle=\"tooltip\" data-placement=\"top\" title=\"Als gesungen markieren\" onclick=\"markEntryAsSung("+row.entry_ID+")\"><i class=\"fas fa-check\"></i></button>&nbsp;<button type=\"button\" class=\"btn btn-danger\" data-toggle=\"tooltip\" data-placement=\"top\" title=\"Eintrag löschen\" onclick=\"confirmDeleteEntry('"+row.Name+"',"+row.entry_ID+")\"><i class=\"fas fa-trash\"></i></button>";
+        if (row.Transferred == 1) {
+            outerHTML = "<button type=\"button\" class=\"btn btn-default\" data-toggle=\"tooltip\" data-placement=\"top\" title=\"Markierung zurückziehen\" onclick=\"event.stopPropagation();$(this).tooltip('dispose');markEntryAsTransferred(" + row.entry_ID + ")\"><i class=\"fas fa-backward\"></i></button>&nbsp;<button type=\"button\" class=\"btn btn-success\" data-toggle=\"tooltip\" data-placement=\"top\" title=\"Als gesungen markieren\" onclick=\"event.stopPropagation();$(this).tooltip('dispose');markEntryAsSung(" + row.entry_ID + ")\"><i class=\"fas fa-check\"></i></button>&nbsp;<button type=\"button\" class=\"btn btn-danger\" data-toggle=\"tooltip\" data-placement=\"top\" title=\"Eintrag löschen\" onclick=\"event.stopPropagation();$(this).tooltip('dispose');confirmDeleteEntry(this.parentElement.parentElement.children[1].innerText," + row.entry_ID + ")\"><i class=\"fas fa-trash\"></i></button>";
         } else {
-            outerHTML = "<button type=\"button\" class=\"btn btn-info\" onclick=\"markEntryAsTransferred("+row.entry_ID+")\"><i class=\"fas fa-exchange-alt\"></i></button>&nbsp;<button type=\"button\" class=\"btn btn-success\" data-toggle=\"tooltip\" data-placement=\"top\" title=\"Als gesungen markieren\" onclick=\"markEntryAsSung("+row.entry_ID+")\"><i class=\"fas fa-check\"></i></button>&nbsp;<button type=\"button\" class=\"btn btn-danger\" data-toggle=\"tooltip\" data-placement=\"top\" title=\"Eintrag löschen\" onclick=\"confirmDeleteEntry('"+row.Name+"',"+row.entry_ID+")\"><i class=\"fas fa-trash\"></i></button>";
+            outerHTML = "<button type=\"button\" class=\"btn btn-info\" data-toggle=\"tooltip\" data-placement=\"top\" title=\"Als übertragen markieren\" onclick=\"event.stopPropagation();$(this).tooltip('dispose');markEntryAsTransferred(" + row.entry_ID + ")\"><i class=\"fas fa-exchange-alt\"></i></button>&nbsp;<button type=\"button\" class=\"btn btn-success\" data-toggle=\"tooltip\" data-placement=\"top\" title=\"Als gesungen markieren\" onclick=\"event.stopPropagation();$(this).tooltip('dispose');markEntryAsSung(" + row.entry_ID + ")\"><i class=\"fas fa-check\"></i></button>&nbsp;<button type=\"button\" class=\"btn btn-danger\" data-toggle=\"tooltip\" data-placement=\"top\" title=\"Eintrag löschen\" onclick=\"event.stopPropagation();$(this).tooltip('dispose');confirmDeleteEntry(this.parentElement.parentElement.children[1].innerText," + row.entry_ID + ")\"><i class=\"fas fa-trash\"></i></button>";
         }
         return outerHTML;
     }
+
+    function CopyFormatter(value, row, index) {
+        let escapedString = value.replace("\"", "\\\"").replace("\'", "\\\'")
+        return "<span onclick='copyAndNotify(this.innerText)'>" + value + "</span>";
+    }
+
     function getIdSelections() {
         return $.map($("#entrytable").bootstrapTable('getSelections'), function (row) {
             return row.entry_ID
         })
     }
+
+    function copyAndNotify(text) {
+        navigator.clipboard.writeText(text).then(function () {
+            let toast = {
+                title: "Kopiert",
+                message: text,
+                status: 1,
+                timeout: 5000
+            };
+            Toast.create(toast);
+        })
+    }
 </script>
 {% endblock %}

backend/templates/settings.html

@@ -10,9 +10,32 @@
         <label for="maxqueue">Maximale Anzahl an Einträgen Insgesamt</label>
         <input type="number" class="form-control" id="maxqueue" name="maxqueue" min=1 value={{app.config['MAX_QUEUE']}}>
     </p>
+    <p>
+        <label for="theme">Aktives Theme</label>
+        <select class="form-control" id="theme" name="theme">
+            {% for theme in themes %}
+            <option value="{{theme}}" {% if theme == config['THEME'] %}selected{% endif %}>{{theme}}</option>
+            {% endfor %}
+        </select>
+    </p>
+    <div class="alert alert-warning" role="alert">
+        <i class="fas fa-exclamation-triangle mr-1"></i>
+        <strong>Warnung:</strong> Änderungen an den folgenden Einstellungen führen zu einer sofortigen Abmeldung!
+    </div>
+    <p>
+        <label for="username">Benutzername</label>
+        <input type="text" class="form-control" id="username" name="username" value={{app.config['BASIC_AUTH_USERNAME']}}>
+    </p>
+    <p>
+        <label for="password">Passwort ändern</label>
+        <input type="password" class="form-control" id="password" name="password">
+    </p>
     <input type="submit" class="btn btn-primary mr-1 mb-2" value="Einstellungen anwenden">
-    <input type="button" class="btn btn-default mr-1 mb-2" onclick="$.get('/writeSettings').done(()=>{alert('Einstellungen gespeichert')}).fail(()=>{alert('Fehler beim Speichern der Einstellungen')})" value="Einstellungen speichern"/>
 </form>
+<details>
+    <summary>Current config:</summary>
+    <pre>{% for key, val in config.items() %}{{key}}: {{val}}<br>{% endfor %}</pre>
+</details>
 {% endblock %}
 {% block extrajs %}
 {% endblock %}

backend/templates/songlist.html

@@ -11,21 +11,52 @@
     <div class="modal-dialog" role="document">
         <div class="modal-content">
             <div class="modal-header">
-                <h5 class="modal-title" id="exampleModalLabel">Auf Liste setzen</h5>
+                <h5 class="modal-title" id="exampleModalLabel">Auf die Liste setzen</h5>
                 <button type="button" class="close" data-dismiss="modal" aria-label="Close">
                     <span aria-hidden="true">&times;</span>
                 </button>
             </div>
+            <div class="songinfo container">
+                <div class="row">
+                    <div class="col">
+                        <h5 id="songTitle"></h5>
+                        <p><span id="songArtist"></span>&nbsp;<span id="songYear"></span></p>
+                    </div>
+                </div>
+                <div class="row">
+                    <div class="col-1">
+                        <p><i class="fas fa-info"></i></p>
+                    </div>
+                    <div class="col" id="indicators">
+                    </div>
+                </div>
+                <div class="row">
+                    <div class="col-1">
+                        <p><i class="fas fa-file-audio"></i></p>
+                    </div>
+                    <div class="col">
+                        <p id="songGenres"></p>
+                    </div>
+                </div>
+                <div class="row">
+                    <div class="col-1">
+                        <p><i class="fas fa-language"></i></p>
+                    </div>
+                    <div class="col">
+                        <p id="songLanguages"></p>
+                    </div>
+                </div>
+            </div>
             <div class="form-group">
                 <form id="nameForm">
                     <div class="modal-body">
-                        <label for="singerNameInput">Sängername</label>
+                        <label for="singerNameInput">Dein Name:</label>
                         <input type="text" class="form-control" id="singerNameInput" placeholder="Max Mustermann"
                             required>
                         <input id="selectedId" name="selectedId" type="hidden" value="">
                     </div>
                     <div class="modal-footer">
-                        <button type="button" class="btn btn-secondary" data-dismiss="modal">Close</button>
+                        <button type="button" class="btn btn-secondary" data-dismiss="modal">Schließen</button>
                         <button type="submit" class="btn btn-primary" id="submitSongButton">Anmelden</button>
                     </div>
                 </form>
@@ -38,35 +69,15 @@
 <script>
     $(document).ready(function () {
         $("#filter").focus();
-        $("#filter").keyup(function () {
-            var value = $(this).val().toLowerCase();
-            //alert(value);
-            if (value.length >= 1) {
-                $.getJSON("/api/songs/compl", { search: value }, function (data) {
-                    var items = [];
-                    $.each(data, function (key, val) {
-                        items.push("<tr><td>" + val[0] + `</td>
-                            <td><button type='button'
-                                    class='btn btn-primary justify-content-center align-content-between enqueueButton'
-                                    data-toggle='modal'
-                                    data-target='#enqueueModal' onclick='setSelectedId(`+ val[1] + `)'><i
-                                        class="fas fa-plus"></i></button></td>
-                        </tr>`)
-                    });
-
-                    $("#songtable").html("")
-                    $(items.join("")).appendTo("#songtable");
-                    entriesAccepted()
-                });
-            } else {
-                $("#songtable").html("")
-            }
-        });
+        $("#filter").keyup(debounce(() => songSearch()));
 
         $("#nameForm").submit(function (e) {
             e.preventDefault();
             submitModal();
         });
+        $('#enqueueModal').on('shown.bs.modal', function (e) {
+            $("#singerNameInput").focus();
+        })
     });
 
 
@@ -79,7 +90,7 @@
         $.ajax({
             type: 'POST',
             url: '/api/enqueue',
-            data: JSON.stringify(data), // or JSON.stringify ({name: 'jonas'}),
+            data: JSON.stringify(data),
             success: success_callback,
             statusCode: {
                 423: blocked_callback
@@ -90,18 +101,112 @@
     }
 
     function setSelectedId(id) {
+        $("#songArtist").html("");
+        $("#songTitle").html("");
+        $("#songYear").html("");
+        $("#indicators")[0].innerHTML = "";
+
         $("#selectedId").attr("value", id);
+        $.getJSON("/api/songs/details/" + id, function (data) {
+            console.log(data);
+            $("#songTitle").html(data["title"]);
+            $("#songArtist").html(data["artist"]);
+            $("#songYear").html(data["year"]);
+            $("#indicators")[0].innerHTML = "";
+
+            let duoindicator_badge = document.createElement("span");
+            duoindicator_badge.classList.add("badge");
+            duoindicator_badge.classList.add("badge-secondary");
+            duoindicator_badge.classList.add("badge-pill");
+            duoindicator_badge.classList.add("mx-1");
+            duoindicator_badge.classList.add("p-2");
+
+            if (data["duo"] == 0) {
+                duoindicator_badge.innerHTML = "Solo";
+                let duoindicator = document.createElement("i");
+                duoindicator.classList.add("fas");
+                duoindicator.classList.add("fa-user");
+                duoindicator.classList.add("ml-1");
+                duoindicator_badge.appendChild(duoindicator);
+                $("#indicators")[0].appendChild(duoindicator_badge)
+            }
+
+            if (data["duo"] == 1) {
+                duoindicator_badge.innerHTML = "Duo";
+                let duoindicator = document.createElement("i");
+                duoindicator.classList.add("fas");
+                duoindicator.classList.add("fa-user-friends");
+                duoindicator.classList.add("ml-1");
+                duoindicator_badge.appendChild(duoindicator);
+                $("#indicators")[0].appendChild(duoindicator_badge)
+            }
+
+            if (data["explicit"] == 1) {
+                let explicitindicator_badge = document.createElement("span");
+                explicitindicator_badge.classList.add("badge");
+                explicitindicator_badge.classList.add("badge-secondary");
+                explicitindicator_badge.classList.add("badge-pill");
+                explicitindicator_badge.classList.add("mx-1");
+                explicitindicator_badge.classList.add("p-2");
+                explicitindicator_badge.innerHTML = "Explicit";
+                let explicitindicator = document.createElement("i");
+                explicitindicator.classList.add("fas");
+                explicitindicator.classList.add("fa-e");
+                explicitindicator.classList.add("ml-1");
+                explicitindicator_badge.appendChild(explicitindicator);
+                $("#indicators")[0].appendChild(explicitindicator_badge)
+            }
+
+            let styles = data["styles"].split(",");
+            let languages = data["languages"].split(",");
+
+            $("#songGenres").html("");
+            $("#songLanguages").html("");
+
+            for (let i = 0; i < styles.length; i++) {
+                let badge = document.createElement("span");
+                badge.classList.add("badge");
+                badge.classList.add("badge-secondary");
+                badge.classList.add("badge-pill");
+                badge.classList.add("mx-1");
+                badge.classList.add("p-2");
+                badge.innerHTML = styles[i];
+                $("#songGenres")[0].appendChild(badge);
+            }
+
+            for (let i = 0; i < languages.length; i++) {
+                let badge = document.createElement("span");
+                badge.classList.add("badge");
+                badge.classList.add("badge-secondary");
+                badge.classList.add("badge-pill");
+                badge.classList.add("mx-1");
+                badge.classList.add("p-2");
+                badge.innerHTML = languages[i];
+                $("#songLanguages")[0].appendChild(badge);
+            }
+        });
     }
 
     function submitModal() {
         var name = $("#singerNameInput").val();
         var id = $("#selectedId").attr("value");
-        enqueue(localStorage.getItem("clientId"),id, name, function () {
+        enqueue(localStorage.getItem("clientId"), id, name, function (response) {
+            console.log(response);
+            entryID = response["entry_id"];
+            toast = {
+                title: "Erfolgreich eingetragen",
+                message: "Du wurdest erfolgreich eingetragen.",
+                status: TOAST_STATUS.SUCCESS,
+                timeout: 5000
+            }
+            Toast.create(toast);
+            console.log("Entry ID: " + entryID);
+            addEntry(entryID);
             $("#enqueueModal").modal('hide');
             window.location.href = '/#end';
         }, function (response) {
             bootbox.alert({
-                message: "Deine Eintragung konnte leider nicht vorgenommen werden.\nGrund: "+response.responseJSON.status,
+                message: "Deine Eintragung konnte leider nicht vorgenommen werden.\nGrund: " + response.responseJSON.status,
             });
             entriesAccepted();
             $("#enqueueModal").modal('hide');
@@ -111,6 +216,76 @@
 
     }
 
+    function songSearch() {
+        let value = $("#filter").val()
+        if (value.length >= 1) {
+            $.getJSON("/api/songs/search", { q: value }, function (data) {
+                var items = [];
+                $("#songtable").html("")
+                $.each(data, function (key, val) {
+                    let itemRow = document.createElement("tr")
+                    let itemCell = document.createElement("td")
+                    itemCell.innerHTML = val["artist"] + ` - ` + val["title"]
+                    itemRow.appendChild(itemCell)
+                    let infoCell = document.createElement("td")
+                    let duoindicator = document.createElement("i")
+                    duoindicator.classList.add("fas")
+                    if (val["duo"] == 0) {
+                        duoindicator.classList.add("fa-user")
+
+                    }
+                    if (val["duo"] == 1) {
+                        duoindicator.classList.add("fa-user-friends")
+                    }
+                    duoindicator.classList.add("ml-1")
+                    duoindicator.classList.add("list-indicator")
+                    infoCell.appendChild(duoindicator)
+                    if (val["explicit"] == 1) {
+                        let explicitindicator = document.createElement("i")
+                        explicitindicator.classList.add("fas")
+                        explicitindicator.classList.add("fa-e")
+                        explicitindicator.classList.add("ml-1")
+                        infoCell.appendChild(explicitindicator)
+                    }
+                    itemRow.appendChild(infoCell)
+                    let buttonCell = document.createElement("td")
+                    let button = document.createElement("button")
+                    button.classList.add("btn")
+                    button.classList.add("btn-primary")
+                    button.classList.add("justify-content-center")
+                    button.classList.add("align-content-between")
+                    button.classList.add("enqueueButton")
+                    button.setAttribute("type", "button")
+                    button.setAttribute("data-toggle", "modal")
+                    button.setAttribute("data-target", "#enqueueModal")
+                    button.setAttribute("onclick", "setSelectedId(" + val["karafun_id"] + ")")
+                    let buttonIcon = document.createElement("i")
+                    buttonIcon.classList.add("fas")
+                    buttonIcon.classList.add("fa-plus")
+                    button.appendChild(buttonIcon)
+                    buttonCell.appendChild(button)
+                    itemRow.appendChild(buttonCell)
+                    $("#songtable").append(itemRow)
+                });
+
+
+                $(items.join("")).appendTo("#songtable");
+                entriesAccepted()
+            });
+        } else {
+            $("#songtable").html("")
+        }
+    }
+
+    function debounce(func, timeout = 300) {
+        let timer;
+        return (...args) => {
+            clearTimeout(timer);
+            timer = setTimeout(() => { func.apply(this, args); }, timeout);
+        };
+    }
+
+
     {% if not auth %}
     function entriesAccepted() {
         $.getJSON("/api/entries/accept", (data, out) => {
@@ -118,10 +293,11 @@
                 $(".enqueueButton").prop("disabled", true)
                 $(".enqueueButton").prop("style", "pointer-events: none;")
                 $(".enqueueButton").wrap("<span class='tooltip-span' tabindex='0' data-toggle='tooltip' data-placement='top'></span>");
-                $(".tooltip-span").prop("title", "Eintragungen sind leider nicht mehr möglich.")
+                $(".tooltip-span").prop("title", "Eintragungen sind leider momentan nicht möglich.")
                 $('[data-toggle="tooltip"]').tooltip()
             } else {
                 $(".enqueueButton").prop("disabled", false)
+
             }
 
         })

build_container.sh

@@ -0,0 +1,39 @@
+#!/bin/bash
+
+# Get username from command line
+if [ $# -eq 0 ]; then
+    echo "No username supplied. Please supply a github username as the first argument."
+    exit 1
+fi
+
+# Store username in variable
+USERNAME=$1
+
+# Check for uncommitted changes
+if ! git diff-index --quiet HEAD --; then
+    echo "You have uncommitted changes. Please commit or stash them and try again."
+    exit 1
+fi
+
+# Get the appropriate version of the container using git
+VERSION=$(git rev-parse --abbrev-ref HEAD)-$(git describe)
+
+# Build the container. Add the version as a tag and as ENV variable SOURCE_VERSION
+docker build -t ghcr.io/$USERNAME/karaoqueue:$VERSION --build-arg SOURCE_VERSION=$VERSION .
+
+# Ask the user if they want to push the container. Confirm Version.
+read -p "Push container to ghcr.io/$USERNAME/karaoqueue:$VERSION? [y/n] " -n 1 -r
+echo
+if [[ $REPLY =~ ^[Yy]$ ]]
+then
+    docker push ghcr.io/$USERNAME/karaoqueue:$VERSION
+fi
+
+# Ask the user if they want to push the container as latest
+read -p "Push container to ghcr.io/$USERNAME/karaoqueue:latest? [y/n] " -n 1 -r
+echo
+if [[ $REPLY =~ ^[Yy]$ ]]
+then
+    docker tag ghcr.io/$USERNAME/karaoqueue:$VERSION ghcr.io/$USERNAME/karaoqueue:latest
+    docker push ghcr.io/$USERNAME/karaoqueue:latest
+fi

docker-compose.prod.yml

@@ -6,23 +6,18 @@ secrets:
 
 services:
   karaoqueue:
-    image: "phillipkhne/karaoqueue:latest"
+    image: "ghcr.io/phoenixtwofive/karaoqueue:v2023.06"
+    build: .
     restart: always
     ports:
       - "127.0.0.1:8081:80" # Please put a reverse proxy in front of this
-    environment:
-      DEPLOYMENT_PLATFORM: Docker
-      DBSTRING: mysql://user:pass@host:3306/database
-      BASIC_AUTH_USERNAME: admin
-      BASIC_AUTH_PASSWORD: changeme
-      ENTRY_QUOTA: 3
-      MAX_QUEUE: 20
+    env_file: .env
   db:
     image: mariadb
     restart: always
-    environment:
-      MARIADB_ROOT_PASSWORD: changeme!
-      MARIADB_ROOT_HOST: localhost
-      MARIADB_DATABASE: karaoqueue
-      MARIADB_USER: karaoqueue
-      MARIADB_PASSWORD: change
+    env_file: .env 
+    volumes:
+    - karaoqueue-db:/var/lib/mysql
+
+volumes:
+  karaoqueue-db: