From a9065279be9640b92a449497341ef8c7422f0d34 Mon Sep 17 00:00:00 2001
From: Chris Fulljames <christian.fulljames@gmail.com>
Date: Sun, 18 Jan 2026 21:57:44 -0500
Subject: [PATCH] Initial work on import queue database

---
 src/littlesongplace/dreams_importer.py    | 50 +++++++++++++++++++++++
 src/littlesongplace/sql/schema_update.sql | 10 +++++
 2 files changed, 60 insertions(+)

diff --git a/src/littlesongplace/dreams_importer.py b/src/littlesongplace/dreams_importer.py
index fcd07cc..9060672 100644
--- a/src/littlesongplace/dreams_importer.py
+++ b/src/littlesongplace/dreams_importer.py
@@ -13,3 +13,53 @@ bp = Blueprint("dreams-importer", __name__, url_prefix="/dreams-importer")
 def dreams_importer():
     return render_template("dreams-importer.html")
 
+@bp.get("/next-in-queue")
+def get_next_in_queue():
+    # Check for pending imports, set error status
+    result = db.query("SELECT * FROM import_queue WHERE status = 1")
+    for row in result:
+        db.query(
+            "UPDATE import_queue SET status = 2 WHERE queueid = ?",
+            [row["queueid"]])
+
+    # Get next queued import
+    result = db.query(
+        """
+        SELECT * FROM import_queue
+        WHERE status = 0
+        LIMIT 1
+        """, one=True)
+
+    response = {"next": None}
+    if result:
+        response["next"] = dict(result)
+
+        # Set pending status
+        db.query(
+            "UPDATE import_queue SET status = 1 WHERE queueid = ?",
+            [result["queueid"]])
+
+    db.commit()
+
+    return response
+
+# TODO: Local/admin only
+@bp.get("/reset-queue")
+def reset_queue():
+    # Reset all pending (1) and error (2) statuses to queued (0)
+    result = db.query("SELECT * FROM import_queue WHERE status = 1 OR status = 2")
+    for row in result:
+        db.query(
+            "UPDATE import_queue SET status = 0 WHERE queueid = ?",
+            [row["queueid"]])
+    db.commit()
+    
+    return {"status": "ok"}
+
+# TODO: Local/admin only
+@bp.get("/delete/<int:queueid>")
+def delete_entry(queueid):
+    db.query("DELETE FROM import_queue WHERE queueid = ?", [queueid])
+    db.commit()
+    return {"status": "ok"}
+
diff --git a/src/littlesongplace/sql/schema_update.sql b/src/littlesongplace/sql/schema_update.sql
index c489d9d..fac1ddc 100644
--- a/src/littlesongplace/sql/schema_update.sql
+++ b/src/littlesongplace/sql/schema_update.sql
@@ -1,3 +1,13 @@
+ALTER TABLE songs ADD COLUMN queueid INTEGER REFERENCES import_queue(queueid) ON DELETE SET NULL;
+
+-- DROP TABLE IF EXISTS import_queue;
+CREATE TABLE import_queue (
+    queueid INTEGER PRIMARY KEY AUTOINCREMENT,
+    indreamsurl TEXT NOT NULL,
+    created TEXT NOT NULL,
+    songid INTEGER NOT NULL REFERENCES songs(songid) ON DELETE CASCADE,
+    status INTEGER NOT NULL
+);
 
 PRAGMA user_version = 8;
 
-- 
2.39.5