VGMQ Full Implementation with Playlist and File Synchronization #24

New Issue

2026-06-11T21:40:17+02:00

Sansan commented

2026-06-11 21:40:17 +02:00

VGMQ Full Implementation with Playlist and File Synchronization

Status: Not Started
Priority: High
Estimate: 8-12 hours

Summary

Implement a complete VGMQ (Video Game Music Quiz) system with:

Database table for VGMQ entries (migrated from existing vgmq table)
CSV data import with Swedish-to-English column mapping
File synchronization between CSV data and Kan Ej/ folder
In-memory playlist management for unknown entries
Current song tracking and metadata editing
MP3 file streaming endpoint
REST API endpoints under /api/v1/vgmq/ with token authentication

Requirements

1. Environment Configuration

Add to .env: VGMQ_PATH=/Users/sebastian/ResilioSync/VGMQ_Test/
Add to .env.test: VGMQ_PATH=/Users/sebastian/projects/MusicServer/testVGMQ/

2. Database Schema

Migration: internal/db/migrations/YYYYMMDDHHMMSS_create_new_vgmq.sql

DROP TABLE IF EXISTS vgmq;
DROP SEQUENCE IF EXISTS vgmq_song_no_seq;
CREATE TABLE vgmq (
    song_number INTEGER PRIMARY KEY,
    game_title VARCHAR(255),
    song_title VARCHAR(255),
    console VARCHAR(100),
    link VARCHAR(500),
    guess_info TEXT,
    is_unknown BOOLEAN DEFAULT false,
    file_path VARCHAR(500),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
CREATE INDEX idx_vgmq_game_title ON vgmq(game_title);
CREATE INDEX idx_vgmq_console ON vgmq(console);
CREATE INDEX idx_vgmq_is_unknown ON vgmq(is_unknown);
CREATE INDEX idx_vgmq_song_number ON vgmq(song_number);

3. SQL Queries

File: internal/db/queries/vgmq.sql

CreateVgmq, GetVgmqBySongNumber, ListAllVgmq, ListUnknownVgmq
UpdateVgmq, DeleteVgmq, DeleteAllVgmq, GetVgmqCount, SearchVgmq
GetUnknownVgmqCount

Update sqlc.yaml: Add vgmq to tables list, run just sqlc-generate

4. Backend Implementation

File: internal/backend/vgmq.go

Types: VGMQ, VGMQCSV, VGMQHandler
Global: vgmqPlaylist, currentVGMQIndex, playlistMutex
Functions: NewVGMQHandler, readCSV, syncFilesWithCSV, SyncFromCSV, GetAllVGMQ, GetVGMQBySongNumber, GetUnknownVGMQ, SearchVGMQ, InitVGMQPlaylist, GetCurrentVGMQ, GetFirstUnknownVGMQ, GetNextUnknownVGMQ, GetPreviousUnknownVGMQ, GetRandomUnknownVGMQ, GetVGMQByNumber, UpdateCurrentVGMQ, GetVGMQPlaylist, GetVGMQCurrentInfo

5. Server Integration

File: internal/server/server.go

Add vgmqHandler to Server struct
Read VGMQ_PATH from env, create handler, init playlist in goroutine

6. HTTP Handlers

File: internal/server/vgmq_handler.go

VGMQResponse type with all fields
12 handlers for all endpoints

7. Routes

File: internal/server/routes.go
All /api/v1/vgmq/* protected by tokenAuthMiddleware

# VGMQ Full Implementation with Playlist and File Synchronization **Status**: Not Started **Priority**: High **Estimate**: 8-12 hours --- ## Summary Implement a complete VGMQ (Video Game Music Quiz) system with: - Database table for VGMQ entries (migrated from existing vgmq table) - CSV data import with Swedish-to-English column mapping - File synchronization between CSV data and Kan Ej/ folder - In-memory playlist management for unknown entries - Current song tracking and metadata editing - MP3 file streaming endpoint - REST API endpoints under /api/v1/vgmq/ with token authentication --- ## Requirements ### 1. Environment Configuration Add to .env: `VGMQ_PATH=/Users/sebastian/ResilioSync/VGMQ_Test/` Add to .env.test: `VGMQ_PATH=/Users/sebastian/projects/MusicServer/testVGMQ/` ### 2. Database Schema Migration: `internal/db/migrations/YYYYMMDDHHMMSS_create_new_vgmq.sql` ```sql DROP TABLE IF EXISTS vgmq; DROP SEQUENCE IF EXISTS vgmq_song_no_seq; CREATE TABLE vgmq ( song_number INTEGER PRIMARY KEY, game_title VARCHAR(255), song_title VARCHAR(255), console VARCHAR(100), link VARCHAR(500), guess_info TEXT, is_unknown BOOLEAN DEFAULT false, file_path VARCHAR(500), created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(), updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() ); CREATE INDEX idx_vgmq_game_title ON vgmq(game_title); CREATE INDEX idx_vgmq_console ON vgmq(console); CREATE INDEX idx_vgmq_is_unknown ON vgmq(is_unknown); CREATE INDEX idx_vgmq_song_number ON vgmq(song_number); ``` ### 3. SQL Queries File: `internal/db/queries/vgmq.sql` - CreateVgmq, GetVgmqBySongNumber, ListAllVgmq, ListUnknownVgmq - UpdateVgmq, DeleteVgmq, DeleteAllVgmq, GetVgmqCount, SearchVgmq - GetUnknownVgmqCount Update sqlc.yaml: Add `vgmq` to tables list, run `just sqlc-generate` ### 4. Backend Implementation File: `internal/backend/vgmq.go` Types: VGMQ, VGMQCSV, VGMQHandler Global: vgmqPlaylist, currentVGMQIndex, playlistMutex Functions: NewVGMQHandler, readCSV, syncFilesWithCSV, SyncFromCSV, GetAllVGMQ, GetVGMQBySongNumber, GetUnknownVGMQ, SearchVGMQ, InitVGMQPlaylist, GetCurrentVGMQ, GetFirstUnknownVGMQ, GetNextUnknownVGMQ, GetPreviousUnknownVGMQ, GetRandomUnknownVGMQ, GetVGMQByNumber, UpdateCurrentVGMQ, GetVGMQPlaylist, GetVGMQCurrentInfo ### 5. Server Integration File: `internal/server/server.go` - Add vgmqHandler to Server struct - Read VGMQ_PATH from env, create handler, init playlist in goroutine ### 6. HTTP Handlers File: `internal/server/vgmq_handler.go` - VGMQResponse type with all fields - 12 handlers for all endpoints ### 7. Routes File: `internal/server/routes.go` All /api/v1/vgmq/* protected by tokenAuthMiddleware

Sign in to join this conversation.

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: Sansan/MusicServer#24