From 62c1511ae6a1b132b2428d0eb8f16a55eb70d004 Mon Sep 17 00:00:00 2001
From: Chris Fulljames <christianfulljames@gmail.com>
Date: Tue, 11 Feb 2025 07:30:41 -0500
Subject: [PATCH] Initial work on playlists

---
 main.py           | 41 +++++++++++++++++++++++++++++++++++++++++
 schema.sql        |  7 +++++--
 schema_update.sql | 26 ++++++++++++++++++++++----
 3 files changed, 68 insertions(+), 6 deletions(-)

diff --git a/main.py b/main.py
index d777b48..f615961 100644
--- a/main.py
+++ b/main.py
@@ -690,6 +690,47 @@ def new_activity():
 def site_news():
     return render_template("news.html")
 
+@app.post("/create-playlist")
+def create_playlist():
+    if not "userid" in session:
+        return redirect("/login")
+
+    name = request.form["name"]
+    if not name:
+        flash_and_log("Playlist must have a name", "error")
+        return redirect(request.referrer)
+
+    timestamp = datetime.now(timezone.utc).isoformat()
+
+    private = request.form["type"] == "private"
+
+    query_db(
+        "insert into playlists (created, updated, userid, name, private) values (?, ?, ?, ?, ?)",
+        args=[
+            timestamp,
+            timestamp,
+            session["userid"],
+            name,
+            private,
+        ]
+    )
+
+@app.post("/delete-playlist/<int:playlistid>")
+def delete_playlist(playlistid):
+    query_db("delete from playlists where playlistid = ?", args=[playlistid])
+
+@app.get("/edit-playlist")
+def edit_playlist_get():
+    abort(404)
+
+@app.post("/edit-playlist")
+def edit_playlist_post():
+    abort(404)
+
+@app.get("/playlists/<int:playlistid>")
+def playlists(playlistid):
+    abort(404)
+
 def flash_and_log(msg, category=None):
     flash(msg, category)
     username = session["username"] if "username" in session else "N/A"
diff --git a/schema.sql b/schema.sql
index f70f7df..fb7695f 100644
--- a/schema.sql
+++ b/schema.sql
@@ -5,7 +5,10 @@ CREATE TABLE users (
     username TEXT UNIQUE NOT NULL,
     password BLOB NOT NULL,
     bio TEXT,
-    activitytime TEXT
+    activitytime TEXT,
+    bgcolor TEXT,
+    fgcolor TEXT,
+    accolor TEXT,
 );
 CREATE INDEX users_by_name ON users(username);
 
@@ -63,5 +66,5 @@ CREATE TABLE song_comment_notifications (
 );
 CREATE INDEX idx_song_comment_notifications_by_target ON song_comment_notifications(targetuserid);
 
-PRAGMA user_version = 1;
+PRAGMA user_version = 2;
 
diff --git a/schema_update.sql b/schema_update.sql
index 598e8ba..a153516 100644
--- a/schema_update.sql
+++ b/schema_update.sql
@@ -1,6 +1,24 @@
-ALTER TABLE users ADD COLUMN bgcolor TEXT;
-ALTER TABLE users ADD COLUMN fgcolor TEXT;
-ALTER TABLE users ADD COLUMN accolor TEXT;
+CREATE TABLE playlists (
+    playlistid INTEGER PRIMARY KEY,
+    created TEXT NOT NULL,
+    updated TEXT NOT NULL,
+    userid INTEGER NOT NULL,
+    name TEXT NOT NULL,
+    private INTEGER NOT NULL,
 
-PRAGMA user_version = 2;
+    FOREIGN KEY(userid) REFERENCES users(userid)
+);
+
+CREATE TABLE playlist_songs (
+    playlistid INTEGER NOT NULL,
+    position INTEGER NOT NULL,
+    songid INTEGER NOT NULL,
+
+    PRIMARY KEY(playlistid, position),
+    FOREIGN KEY(playlistid) REFERENCES playlists(playlistid),
+    FOREIGN KEY(songid) REFERENCES songs(songid)
+);
+CREATE INDEX playlist_songs_by_playlist ON playlist_songs(playlistid);
+
+PRAGMA user_version = 3;
 
-- 
2.39.5