minster586
b9a2cc584b
This is the start upload I figured this is a way for me to easily tweak with radio DJ and get the album art
760 lines
31 KiB
JavaScript
760 lines
31 KiB
JavaScript
'use strict';
|
|
|
|
/**
|
|
* rdj-art-app — Album artwork manager for RadioDJ MySQL databases.
|
|
*
|
|
* - Local accounts + RadioDJ connection profiles stored in embedded SQLite.
|
|
* - First registered user becomes 'admin' (user management + profile cloning).
|
|
* - Artwork lookup via 100% free APIs: iTunes Search, fallback MusicBrainz +
|
|
* Cover Art Archive. No API keys required.
|
|
* - Confirmed artwork is downloaded, renamed to the base audio filename
|
|
* ("01 Track.mp3" -> "01 Track.jpg") and streamed back as a ZIP, with an
|
|
* optional UPDATE of the RadioDJ `songs.image` column.
|
|
*/
|
|
|
|
const path = require('path');
|
|
const fs = require('fs');
|
|
const crypto = require('crypto');
|
|
const express = require('express');
|
|
const Database = require('better-sqlite3');
|
|
const mysql = require('mysql2/promise');
|
|
const archiver = require('archiver');
|
|
|
|
// ---------------------------------------------------------------------------
|
|
// Configuration
|
|
// ---------------------------------------------------------------------------
|
|
const PORT = Number.parseInt(process.env.PORT || '3000', 10);
|
|
const DATA_DIR = process.env.DATA_DIR || path.join(__dirname, 'data');
|
|
const SESSION_TTL_MS = 7 * 24 * 60 * 60 * 1000; // 7 days
|
|
const USER_AGENT = 'rdj-art-app/1.0 (open-source; contact: local-admin)';
|
|
const FETCH_TIMEOUT_MS = 12000;
|
|
const MAX_IMAGE_BYTES = 15 * 1024 * 1024;
|
|
|
|
fs.mkdirSync(DATA_DIR, { recursive: true });
|
|
|
|
/**
|
|
* APP_SECRET encrypts stored RadioDJ MySQL passwords. In production always set
|
|
* it via the environment. For convenience a random secret is generated and
|
|
* persisted in the data directory on first boot (never commit that file).
|
|
*/
|
|
function loadSecret() {
|
|
if (process.env.APP_SECRET && process.env.APP_SECRET.length >= 16) {
|
|
return process.env.APP_SECRET;
|
|
}
|
|
const secretFile = path.join(DATA_DIR, '.app-secret');
|
|
try {
|
|
const existing = fs.readFileSync(secretFile, 'utf8').trim();
|
|
if (existing) {
|
|
console.warn('[security] APP_SECRET not set (or too short) — reusing secret from data/.app-secret');
|
|
return existing;
|
|
}
|
|
} catch { /* first boot */ }
|
|
const generated = crypto.randomBytes(32).toString('hex');
|
|
fs.writeFileSync(secretFile, generated, { mode: 0o600 });
|
|
console.warn('[security] APP_SECRET not set — generated a random secret at data/.app-secret. Set APP_SECRET in production!');
|
|
return generated;
|
|
}
|
|
const APP_SECRET = loadSecret();
|
|
const ENC_KEY = crypto.scryptSync(APP_SECRET, 'rdj-art-app/enc/v1', 32);
|
|
|
|
// ---------------------------------------------------------------------------
|
|
// Crypto helpers
|
|
// ---------------------------------------------------------------------------
|
|
function encryptSecret(plain) {
|
|
const iv = crypto.randomBytes(12);
|
|
const cipher = crypto.createCipheriv('aes-256-gcm', ENC_KEY, iv);
|
|
const data = Buffer.concat([cipher.update(String(plain), 'utf8'), cipher.final()]);
|
|
return JSON.stringify({
|
|
iv: iv.toString('base64'),
|
|
tag: cipher.getAuthTag().toString('base64'),
|
|
data: data.toString('base64'),
|
|
});
|
|
}
|
|
|
|
function decryptSecret(payload) {
|
|
const { iv, tag, data } = JSON.parse(payload);
|
|
const decipher = crypto.createDecipheriv('aes-256-gcm', ENC_KEY, Buffer.from(iv, 'base64'));
|
|
decipher.setAuthTag(Buffer.from(tag, 'base64'));
|
|
return Buffer.concat([decipher.update(Buffer.from(data, 'base64')), decipher.final()]).toString('utf8');
|
|
}
|
|
|
|
function hashPassword(password) {
|
|
const salt = crypto.randomBytes(16);
|
|
const hash = crypto.scryptSync(password, salt, 64, { N: 16384, r: 8, p: 1 });
|
|
return ['scrypt', 16384, 8, 1, salt.toString('base64'), hash.toString('base64')].join('$');
|
|
}
|
|
|
|
function verifyPassword(password, stored) {
|
|
try {
|
|
const [algo, N, r, p, saltB64, hashB64] = String(stored).split('$');
|
|
if (algo !== 'scrypt') return false;
|
|
const expected = Buffer.from(hashB64, 'base64');
|
|
const actual = crypto.scryptSync(password, Buffer.from(saltB64, 'base64'), expected.length, {
|
|
N: Number(N), r: Number(r), p: Number(p),
|
|
});
|
|
return crypto.timingSafeEqual(expected, actual);
|
|
} catch {
|
|
return false;
|
|
}
|
|
}
|
|
|
|
// ---------------------------------------------------------------------------
|
|
// SQLite (users, profiles, sessions)
|
|
// ---------------------------------------------------------------------------
|
|
const db = new Database(path.join(DATA_DIR, 'app.db'));
|
|
db.pragma('journal_mode = WAL');
|
|
db.pragma('foreign_keys = ON');
|
|
db.exec(`
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
username TEXT NOT NULL UNIQUE COLLATE NOCASE,
|
|
password_hash TEXT NOT NULL,
|
|
role TEXT NOT NULL DEFAULT 'user' CHECK (role IN ('admin','user')),
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
);
|
|
CREATE TABLE IF NOT EXISTS profiles (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
name TEXT NOT NULL,
|
|
host TEXT NOT NULL,
|
|
port INTEGER NOT NULL DEFAULT 3306,
|
|
db_name TEXT NOT NULL,
|
|
db_user TEXT NOT NULL,
|
|
db_pass_enc TEXT NOT NULL,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
);
|
|
CREATE TABLE IF NOT EXISTS sessions (
|
|
token TEXT PRIMARY KEY,
|
|
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
created_at INTEGER NOT NULL,
|
|
expires_at INTEGER NOT NULL
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_profiles_user ON profiles(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_sessions_user ON sessions(user_id);
|
|
`);
|
|
|
|
// Purge expired sessions hourly.
|
|
setInterval(() => {
|
|
try { db.prepare('DELETE FROM sessions WHERE expires_at < ?').run(Date.now()); } catch { /* ignore */ }
|
|
}, 60 * 60 * 1000).unref();
|
|
|
|
// ---------------------------------------------------------------------------
|
|
// Small utilities
|
|
// ---------------------------------------------------------------------------
|
|
const asyncH = fn => (req, res, next) => Promise.resolve(fn(req, res, next)).catch(next);
|
|
|
|
function parseCookies(req) {
|
|
const out = {};
|
|
for (const part of (req.headers.cookie || '').split(';')) {
|
|
const i = part.indexOf('=');
|
|
if (i > -1) out[part.slice(0, i).trim()] = decodeURIComponent(part.slice(i + 1).trim());
|
|
}
|
|
return out;
|
|
}
|
|
|
|
function publicUser(u) {
|
|
return { id: u.id, username: u.username, role: u.role, createdAt: u.created_at };
|
|
}
|
|
|
|
/** Base name of an audio file without extension; handles / and \ separators. */
|
|
function audioBaseName(p) {
|
|
if (!p) return '';
|
|
const norm = String(p).replace(/\\/g, '/');
|
|
const base = norm.slice(norm.lastIndexOf('/') + 1);
|
|
const dot = base.lastIndexOf('.');
|
|
return dot > 0 ? base.slice(0, dot) : base;
|
|
}
|
|
|
|
/** Strip characters that are illegal in file names on Windows/POSIX. */
|
|
function sanitizeFilename(name) {
|
|
const cleaned = String(name)
|
|
// eslint-disable-next-line no-control-regex
|
|
.replace(/[<>:"/\\|?*\x00-\x1f]/g, '')
|
|
.replace(/\s+/g, ' ')
|
|
.trim()
|
|
.slice(0, 150);
|
|
return cleaned || 'track';
|
|
}
|
|
|
|
async function fetchWithTimeout(url, options = {}, timeoutMs = FETCH_TIMEOUT_MS) {
|
|
const controller = new AbortController();
|
|
const timer = setTimeout(() => controller.abort(), timeoutMs);
|
|
try {
|
|
return await fetch(url, { ...options, signal: controller.signal, redirect: 'follow' });
|
|
} finally {
|
|
clearTimeout(timer);
|
|
}
|
|
}
|
|
|
|
async function fetchJson(url, headers = {}) {
|
|
const res = await fetchWithTimeout(url, { headers });
|
|
if (!res.ok) throw new Error(`HTTP ${res.status} from ${new URL(url).host}`);
|
|
return res.json();
|
|
}
|
|
|
|
async function downloadImage(url) {
|
|
if (typeof url !== 'string' || !/^https?:\/\//i.test(url)) throw new Error('Invalid image URL.');
|
|
const res = await fetchWithTimeout(
|
|
url,
|
|
{ headers: { 'User-Agent': USER_AGENT, Accept: 'image/*' } },
|
|
20000,
|
|
);
|
|
if (!res.ok) throw new Error(`Download failed (HTTP ${res.status}).`);
|
|
const type = (res.headers.get('content-type') || '').toLowerCase();
|
|
if (!type.startsWith('image/')) throw new Error(`URL did not return an image (${type || 'unknown content-type'}).`);
|
|
const ext = type.includes('png') ? '.png' : type.includes('webp') ? '.webp' : '.jpg';
|
|
const buffer = Buffer.from(await res.arrayBuffer());
|
|
if (buffer.length === 0) throw new Error('Empty image.');
|
|
if (buffer.length > MAX_IMAGE_BYTES) throw new Error('Image exceeds 15 MB limit.');
|
|
return { buffer, ext };
|
|
}
|
|
|
|
/** Run async fn over items with bounded concurrency, preserving order. */
|
|
async function mapLimit(items, limit, fn) {
|
|
const out = new Array(items.length);
|
|
let i = 0;
|
|
await Promise.all(
|
|
Array.from({ length: Math.min(limit, items.length) }, async () => {
|
|
while (i < items.length) {
|
|
const idx = i++;
|
|
out[idx] = await fn(items[idx], idx);
|
|
}
|
|
}),
|
|
);
|
|
return out;
|
|
}
|
|
|
|
// ---------------------------------------------------------------------------
|
|
// Sessions / auth middleware
|
|
// ---------------------------------------------------------------------------
|
|
function getSessionUser(req) {
|
|
const token = parseCookies(req).rdj_session;
|
|
if (!token) return null;
|
|
const row = db.prepare(`
|
|
SELECT u.id, u.username, u.role, u.created_at, s.expires_at AS exp
|
|
FROM sessions s JOIN users u ON u.id = s.user_id
|
|
WHERE s.token = ?`).get(token);
|
|
if (!row) return null;
|
|
if (row.exp < Date.now()) {
|
|
db.prepare('DELETE FROM sessions WHERE token = ?').run(token);
|
|
return null;
|
|
}
|
|
return { id: row.id, username: row.username, role: row.role, created_at: row.created_at };
|
|
}
|
|
|
|
function createSession(res, userId) {
|
|
const token = crypto.randomBytes(32).toString('base64url');
|
|
const now = Date.now();
|
|
db.prepare('INSERT INTO sessions (token, user_id, created_at, expires_at) VALUES (?,?,?,?)')
|
|
.run(token, userId, now, now + SESSION_TTL_MS);
|
|
const secure = process.env.COOKIE_SECURE === '1' ? '; Secure' : '';
|
|
res.setHeader(
|
|
'Set-Cookie',
|
|
`rdj_session=${token}; Path=/; HttpOnly; SameSite=Lax; Max-Age=${Math.floor(SESSION_TTL_MS / 1000)}${secure}`,
|
|
);
|
|
}
|
|
|
|
function requireAuth(req, res, next) {
|
|
const user = getSessionUser(req);
|
|
if (!user) return res.status(401).json({ error: 'Authentication required.' });
|
|
req.user = user;
|
|
next();
|
|
}
|
|
|
|
function requireAdmin(req, res, next) {
|
|
if (!req.user || req.user.role !== 'admin') return res.status(403).json({ error: 'Admin role required.' });
|
|
next();
|
|
}
|
|
|
|
// Naive in-memory rate limiting for auth endpoints (20 attempts / 5 min / IP).
|
|
const authAttempts = new Map();
|
|
function authRateLimit(req, res, next) {
|
|
const key = req.ip || 'unknown';
|
|
const now = Date.now();
|
|
const rec = authAttempts.get(key) || { count: 0, reset: now + 5 * 60 * 1000 };
|
|
if (now > rec.reset) { rec.count = 0; rec.reset = now + 5 * 60 * 1000; }
|
|
rec.count += 1;
|
|
authAttempts.set(key, rec);
|
|
if (rec.count > 20) return res.status(429).json({ error: 'Too many attempts. Try again later.' });
|
|
next();
|
|
}
|
|
|
|
// ---------------------------------------------------------------------------
|
|
// RadioDJ profiles / dynamic MySQL pools
|
|
// ---------------------------------------------------------------------------
|
|
function validateProfileInput(body) {
|
|
const errors = [];
|
|
const name = String(body.name || '').trim();
|
|
const host = String(body.host || '').trim();
|
|
const port = Number.parseInt(body.port ?? '3306', 10);
|
|
const dbName = String(body.database || '').trim();
|
|
const dbUser = String(body.dbUser || '').trim();
|
|
const dbPass = body.dbPassword != null ? String(body.dbPassword) : null;
|
|
if (!name || name.length > 64) errors.push('Profile name is required (max 64 chars).');
|
|
if (!host || host.length > 255) errors.push('Host is required.');
|
|
if (!Number.isInteger(port) || port < 1 || port > 65535) errors.push('Port must be between 1 and 65535.');
|
|
if (!dbName) errors.push('Database name is required.');
|
|
if (!dbUser) errors.push('Database user is required.');
|
|
return { errors, value: { name, host, port, dbName, dbUser, dbPass } };
|
|
}
|
|
|
|
function getOwnedProfile(userId, profileId) {
|
|
return db.prepare('SELECT * FROM profiles WHERE id = ? AND user_id = ?').get(profileId, userId);
|
|
}
|
|
|
|
function profileToJson(p) {
|
|
return { id: p.id, name: p.name, host: p.host, port: p.port, database: p.db_name, dbUser: p.db_user, createdAt: p.created_at };
|
|
}
|
|
|
|
/** Create a temporary MySQL pool for the selected profile and always close it. */
|
|
async function withProfilePool(profile, fn) {
|
|
const pool = mysql.createPool({
|
|
host: profile.host,
|
|
port: profile.port,
|
|
user: profile.db_user,
|
|
password: decryptSecret(profile.db_pass_enc),
|
|
database: profile.db_name,
|
|
waitForConnections: true,
|
|
connectionLimit: 4,
|
|
connectTimeout: 8000,
|
|
charset: 'utf8mb4',
|
|
});
|
|
try {
|
|
return await fn(pool);
|
|
} finally {
|
|
await pool.end().catch(() => {});
|
|
}
|
|
}
|
|
|
|
/**
|
|
* RadioDJ tracks that need artwork. Besides NULL/empty values, RadioDJ assigns
|
|
* default placeholder file names (e.g. 'no_cover_image.jpg', 'no_image.jpg',
|
|
* 'no-cover.jpg') to songs imported without artwork — treat those as missing.
|
|
*/
|
|
const MISSING_ARTWORK_WHERE = `
|
|
song_type = 0
|
|
AND (
|
|
image IS NULL
|
|
OR image = ''
|
|
OR LOWER(image) LIKE '%no_cover%'
|
|
OR LOWER(image) LIKE '%no_image%'
|
|
OR LOWER(image) LIKE '%no-cover%'
|
|
)`;
|
|
|
|
/** True when a RadioDJ `image` value is a known default placeholder. */
|
|
function isPlaceholderImage(image) {
|
|
const v = String(image || '').toLowerCase();
|
|
return v.includes('no_cover') || v.includes('no_image') || v.includes('no-cover');
|
|
}
|
|
|
|
// ---------------------------------------------------------------------------
|
|
// Express app
|
|
// ---------------------------------------------------------------------------
|
|
const app = express();
|
|
app.disable('x-powered-by');
|
|
app.set('trust proxy', true);
|
|
app.use(express.json({ limit: '2mb' }));
|
|
app.use((req, res, next) => {
|
|
res.setHeader('X-Content-Type-Options', 'nosniff');
|
|
res.setHeader('X-Frame-Options', 'DENY');
|
|
res.setHeader('Referrer-Policy', 'same-origin');
|
|
next();
|
|
});
|
|
app.use(express.static(path.join(__dirname, 'public'), {
|
|
maxAge: '1h',
|
|
index: 'index.html',
|
|
setHeaders: (res, filePath) => {
|
|
// Never serve stale HTML — always revalidate (ETag keeps it cheap).
|
|
if (filePath.endsWith('.html')) res.setHeader('Cache-Control', 'no-cache');
|
|
},
|
|
}));
|
|
|
|
// ------------------------------ Auth --------------------------------------
|
|
const USERNAME_RE = /^[A-Za-z0-9_.-]{3,32}$/;
|
|
|
|
app.post('/api/auth/register', authRateLimit, asyncH(async (req, res) => {
|
|
const { username, password } = req.body || {};
|
|
if (!USERNAME_RE.test(String(username || ''))) {
|
|
return res.status(400).json({ error: 'Username must be 3-32 chars (letters, numbers, _ . -).' });
|
|
}
|
|
if (typeof password !== 'string' || password.length < 8) {
|
|
return res.status(400).json({ error: 'Password must be at least 8 characters.' });
|
|
}
|
|
const userCount = db.prepare('SELECT COUNT(*) AS c FROM users').get().c;
|
|
const role = userCount === 0 ? 'admin' : 'user';
|
|
try {
|
|
const info = db.prepare('INSERT INTO users (username, password_hash, role) VALUES (?,?,?)')
|
|
.run(String(username).trim(), hashPassword(password), role);
|
|
const user = db.prepare('SELECT * FROM users WHERE id = ?').get(info.lastInsertRowid);
|
|
createSession(res, user.id);
|
|
res.status(201).json({ user: publicUser(user) });
|
|
} catch (e) {
|
|
if (String(e.message).includes('UNIQUE')) return res.status(409).json({ error: 'Username already taken.' });
|
|
throw e;
|
|
}
|
|
}));
|
|
|
|
app.post('/api/auth/login', authRateLimit, asyncH(async (req, res) => {
|
|
const { username, password } = req.body || {};
|
|
const user = db.prepare('SELECT * FROM users WHERE username = ?').get(String(username || '').trim());
|
|
if (!user || !verifyPassword(String(password || ''), user.password_hash)) {
|
|
return res.status(401).json({ error: 'Invalid username or password.' });
|
|
}
|
|
createSession(res, user.id);
|
|
res.json({ user: publicUser(user) });
|
|
}));
|
|
|
|
app.post('/api/auth/logout', (req, res) => {
|
|
const token = parseCookies(req).rdj_session;
|
|
if (token) db.prepare('DELETE FROM sessions WHERE token = ?').run(token);
|
|
res.setHeader('Set-Cookie', 'rdj_session=; Path=/; HttpOnly; SameSite=Lax; Max-Age=0');
|
|
res.json({ ok: true });
|
|
});
|
|
|
|
app.get('/api/auth/me', (req, res) => {
|
|
const user = getSessionUser(req);
|
|
if (!user) return res.status(401).json({ error: 'Not signed in.' });
|
|
res.json({ user: publicUser(user) });
|
|
});
|
|
|
|
// ---------------------------- Profiles ------------------------------------
|
|
app.get('/api/profiles', requireAuth, (req, res) => {
|
|
const rows = db.prepare('SELECT * FROM profiles WHERE user_id = ? ORDER BY name').all(req.user.id);
|
|
res.json({ profiles: rows.map(profileToJson) });
|
|
});
|
|
|
|
app.post('/api/profiles', requireAuth, asyncH(async (req, res) => {
|
|
const { errors, value } = validateProfileInput(req.body || {});
|
|
if (!value.dbPass) errors.push('Database password is required.');
|
|
if (errors.length) return res.status(400).json({ error: errors.join(' ') });
|
|
const info = db.prepare(`
|
|
INSERT INTO profiles (user_id, name, host, port, db_name, db_user, db_pass_enc)
|
|
VALUES (?,?,?,?,?,?,?)`)
|
|
.run(req.user.id, value.name, value.host, value.port, value.dbName, value.dbUser, encryptSecret(value.dbPass));
|
|
res.status(201).json({ profile: profileToJson(db.prepare('SELECT * FROM profiles WHERE id = ?').get(info.lastInsertRowid)) });
|
|
}));
|
|
|
|
app.put('/api/profiles/:id', requireAuth, asyncH(async (req, res) => {
|
|
const existing = getOwnedProfile(req.user.id, req.params.id);
|
|
if (!existing) return res.status(404).json({ error: 'Profile not found.' });
|
|
const { errors, value } = validateProfileInput(req.body || {});
|
|
if (errors.length) return res.status(400).json({ error: errors.join(' ') });
|
|
// Blank password keeps the stored one.
|
|
const enc = value.dbPass ? encryptSecret(value.dbPass) : existing.db_pass_enc;
|
|
db.prepare('UPDATE profiles SET name=?, host=?, port=?, db_name=?, db_user=?, db_pass_enc=? WHERE id=?')
|
|
.run(value.name, value.host, value.port, value.dbName, value.dbUser, enc, existing.id);
|
|
res.json({ profile: profileToJson(db.prepare('SELECT * FROM profiles WHERE id = ?').get(existing.id)) });
|
|
}));
|
|
|
|
app.delete('/api/profiles/:id', requireAuth, (req, res) => {
|
|
const existing = getOwnedProfile(req.user.id, req.params.id);
|
|
if (!existing) return res.status(404).json({ error: 'Profile not found.' });
|
|
db.prepare('DELETE FROM profiles WHERE id = ?').run(existing.id);
|
|
res.json({ ok: true });
|
|
});
|
|
|
|
app.post('/api/profiles/:id/test', requireAuth, asyncH(async (req, res) => {
|
|
const profile = getOwnedProfile(req.user.id, req.params.id);
|
|
if (!profile) return res.status(404).json({ error: 'Profile not found.' });
|
|
try {
|
|
const result = await withProfilePool(profile, async pool => {
|
|
const [[ver]] = await pool.query('SELECT VERSION() AS version');
|
|
const [[cnt]] = await pool.query(
|
|
`SELECT COUNT(*) AS c FROM songs WHERE ${MISSING_ARTWORK_WHERE}`,
|
|
);
|
|
return { version: ver.version, missingArtwork: Number(cnt.c) };
|
|
});
|
|
res.json({ ok: true, ...result });
|
|
} catch (e) {
|
|
res.status(502).json({ ok: false, error: `Connection failed: ${e.message}` });
|
|
}
|
|
}));
|
|
|
|
// ----------------------------- Tracks -------------------------------------
|
|
app.get('/api/tracks', requireAuth, asyncH(async (req, res) => {
|
|
const profile = getOwnedProfile(req.user.id, req.query.profileId);
|
|
if (!profile) return res.status(404).json({ error: 'Profile not found.' });
|
|
const rawLimit = String(req.query.limit || '25');
|
|
const fetchAll = rawLimit === 'all';
|
|
const limit = fetchAll ? null : Math.min(Math.max(Number.parseInt(rawLimit, 10) || 25, 1), 1000);
|
|
try {
|
|
const payload = await withProfilePool(profile, async pool => {
|
|
const [[cnt]] = await pool.query(`SELECT COUNT(*) AS c FROM songs WHERE ${MISSING_ARTWORK_WHERE}`);
|
|
const totalTracks = Number(cnt.c);
|
|
const totalPages = fetchAll ? 1 : Math.max(1, Math.ceil(totalTracks / limit));
|
|
const page = fetchAll ? 1 : Math.min(Math.max(Number.parseInt(req.query.page || '1', 10) || 1, 1), totalPages);
|
|
const offset = fetchAll ? 0 : (page - 1) * limit;
|
|
const sql = `SELECT id, artist, title, album, path, image
|
|
FROM songs
|
|
WHERE ${MISSING_ARTWORK_WHERE}
|
|
ORDER BY artist ASC, title ASC` + (fetchAll ? '' : '\n LIMIT ? OFFSET ?');
|
|
const rows = await pool.query(sql, fetchAll ? [] : [limit, offset]).then(([r]) => r);
|
|
return { rows, totalTracks, page, totalPages };
|
|
});
|
|
res.json({
|
|
tracks: payload.rows.map(r => ({
|
|
id: r.id,
|
|
artist: r.artist || '',
|
|
title: r.title || '',
|
|
album: r.album || '',
|
|
path: r.path || '',
|
|
audioBase: audioBaseName(r.path),
|
|
image: r.image || '',
|
|
placeholder: isPlaceholderImage(r.image),
|
|
})),
|
|
totalTracks: payload.totalTracks,
|
|
page: payload.page,
|
|
totalPages: payload.totalPages,
|
|
});
|
|
} catch (e) {
|
|
res.status(502).json({ error: `Database error: ${e.message}` });
|
|
}
|
|
}));
|
|
|
|
// ------------------------ Artwork search (free) ----------------------------
|
|
function dedupeResults(list) {
|
|
const seen = new Set();
|
|
const out = [];
|
|
for (const r of list) {
|
|
if (r.full && !seen.has(r.full)) { seen.add(r.full); out.push(r); }
|
|
}
|
|
return out;
|
|
}
|
|
|
|
function escapeLucene(s) {
|
|
return String(s).replace(/[+\-!(){}[\]^"~*?:\\/]|&&|\|\|/g, ' ').replace(/\s+/g, ' ').trim();
|
|
}
|
|
|
|
app.post('/api/artwork/search', requireAuth, asyncH(async (req, res) => {
|
|
const artist = String(req.body?.artist || '').trim();
|
|
const title = String(req.body?.title || '').trim();
|
|
const album = String(req.body?.album || '').trim();
|
|
const source = ['auto', 'itunes', 'deezer', 'musicbrainz'].includes(req.body?.source) ? req.body.source : 'auto';
|
|
if (!artist && !title && !album) return res.status(400).json({ error: 'Provide an artist and/or title.' });
|
|
const results = [];
|
|
|
|
// 1) iTunes Search API — free, no key.
|
|
if (source === 'auto' || source === 'itunes') {
|
|
try {
|
|
const term = [artist, album || title].filter(Boolean).join(' ');
|
|
const data = await fetchJson(
|
|
`https://itunes.apple.com/search?media=music&entity=album&limit=10&term=${encodeURIComponent(term)}`,
|
|
);
|
|
for (const item of data.results || []) {
|
|
if (!item.artworkUrl100) continue;
|
|
results.push({
|
|
source: 'itunes',
|
|
sourceLabel: 'iTunes API',
|
|
artist: item.artistName || artist,
|
|
album: item.collectionName || '',
|
|
thumb: item.artworkUrl100.replace('100x100bb', '200x200bb'),
|
|
full: item.artworkUrl100.replace('100x100bb', '1200x1200bb'),
|
|
});
|
|
}
|
|
} catch (e) {
|
|
console.warn('[artwork] iTunes search failed:', e.message);
|
|
}
|
|
}
|
|
|
|
// 2) Deezer API — free, no key.
|
|
if (source === 'auto' || source === 'deezer') {
|
|
if (source === 'deezer' || results.length < 3) {
|
|
try {
|
|
const q = [artist, album || title].filter(Boolean).join(' ');
|
|
const dz = await fetchJson(`https://api.deezer.com/search/album?limit=10&q=${encodeURIComponent(q)}`);
|
|
for (const d of dz.data || []) {
|
|
const cover = d.cover_xl || d.cover_big || d.cover_medium;
|
|
if (!cover) continue;
|
|
results.push({
|
|
source: 'deezer',
|
|
sourceLabel: 'Deezer',
|
|
artist: d.artist?.name || artist,
|
|
album: d.title || '',
|
|
thumb: d.cover_medium || cover,
|
|
full: cover,
|
|
});
|
|
}
|
|
} catch (e) {
|
|
console.warn('[artwork] Deezer search failed:', e.message);
|
|
}
|
|
}
|
|
}
|
|
|
|
// 3) MusicBrainz + Cover Art Archive fallback — free, no key.
|
|
if (source === 'auto' || source === 'musicbrainz') {
|
|
if (source === 'musicbrainz' || results.length < 3) {
|
|
try {
|
|
const clauses = [];
|
|
if (album) clauses.push(`release:"${escapeLucene(album)}"`);
|
|
else if (title) clauses.push(`release:"${escapeLucene(title)}"`);
|
|
if (artist) clauses.push(`artist:"${escapeLucene(artist)}"`);
|
|
const q = clauses.join(' AND ') || escapeLucene(`${artist} ${title}`);
|
|
const mb = await fetchJson(
|
|
`https://musicbrainz.org/ws/2/release/?fmt=json&limit=5&query=${encodeURIComponent(q)}`,
|
|
{ 'User-Agent': USER_AGENT, Accept: 'application/json' },
|
|
);
|
|
for (const rel of (mb.releases || []).slice(0, 4)) {
|
|
try {
|
|
const caa = await fetchJson(
|
|
`https://coverartarchive.org/release/${rel.id}`,
|
|
{ 'User-Agent': USER_AGENT, Accept: 'application/json' },
|
|
);
|
|
const front = (caa.images || []).find(i => i.front) || (caa.images || [])[0];
|
|
if (front && front.image) {
|
|
results.push({
|
|
source: 'musicbrainz',
|
|
sourceLabel: 'Cover Art Archive / MusicBrainz',
|
|
artist: rel['artist-credit']?.[0]?.name || artist,
|
|
album: rel.title || '',
|
|
thumb: front.thumbnails?.small || front.thumbnails?.['250'] || front.image,
|
|
full: front.image,
|
|
});
|
|
}
|
|
} catch { /* release has no cover art */ }
|
|
}
|
|
} catch (e) {
|
|
console.warn('[artwork] MusicBrainz search failed:', e.message);
|
|
}
|
|
}
|
|
}
|
|
|
|
res.json({ results: dedupeResults(results).slice(0, 12) });
|
|
}));
|
|
|
|
// ------------------------ ZIP export + DB update ---------------------------
|
|
app.post('/api/export', requireAuth, asyncH(async (req, res) => {
|
|
const { profileId, updateDb = false, items = [] } = req.body || {};
|
|
if (!Array.isArray(items) || items.length === 0) return res.status(400).json({ error: 'No items provided.' });
|
|
if (items.length > 500) return res.status(400).json({ error: 'Too many items (max 500 per export).' });
|
|
|
|
const report = { generatedAt: new Date().toISOString(), downloaded: [], updated: [], errors: [] };
|
|
|
|
// Download artwork (bounded concurrency), rename to base audio filename.
|
|
const results = await mapLimit(items, 4, async item => {
|
|
const base = sanitizeFilename(item.audioBase || audioBaseName(item.path) || `track-${item.id ?? 'x'}`);
|
|
try {
|
|
const { buffer, ext } = await downloadImage(String(item.imageUrl || ''));
|
|
return { ok: true, base, buffer, ext, songId: Number.isFinite(+item.id) ? +item.id : null };
|
|
} catch (e) {
|
|
return { ok: false, base, id: item.id ?? null, error: e.message };
|
|
}
|
|
});
|
|
|
|
const usedNames = new Set();
|
|
const files = [];
|
|
for (const r of results) {
|
|
if (!r.ok) {
|
|
report.errors.push({ file: r.base, id: r.id, error: r.error });
|
|
continue;
|
|
}
|
|
let name = `${r.base}${r.ext}`;
|
|
let n = 2;
|
|
while (usedNames.has(name.toLowerCase())) name = `${r.base} (${n++})${r.ext}`;
|
|
usedNames.add(name.toLowerCase());
|
|
files.push({ name, buffer: r.buffer, songId: r.songId });
|
|
report.downloaded.push({ name });
|
|
}
|
|
|
|
// Optional: UPDATE the RadioDJ `songs.image` column to the new file name.
|
|
if (updateDb) {
|
|
const profile = profileId ? getOwnedProfile(req.user.id, profileId) : null;
|
|
if (!profile) return res.status(400).json({ error: 'updateDb requires a valid profileId.' });
|
|
try {
|
|
await withProfilePool(profile, async pool => {
|
|
for (const f of files) {
|
|
if (f.songId == null) continue;
|
|
try {
|
|
await pool.query('UPDATE songs SET image = ? WHERE id = ?', [f.name, f.songId]);
|
|
report.updated.push({ id: f.songId, image: f.name });
|
|
} catch (e) {
|
|
report.errors.push({ id: f.songId, image: f.name, error: `DB update failed: ${e.message}` });
|
|
}
|
|
}
|
|
});
|
|
} catch (e) {
|
|
return res.status(502).json({ error: `Database error: ${e.message}` });
|
|
}
|
|
}
|
|
|
|
const stamp = new Date().toISOString().replace(/[:.]/g, '-').slice(0, 19);
|
|
res.setHeader('Content-Type', 'application/zip');
|
|
res.setHeader('Content-Disposition', `attachment; filename="rdj-artwork-${stamp}.zip"`);
|
|
const archive = archiver('zip', { zlib: { level: 9 } });
|
|
archive.on('error', err => {
|
|
console.error('[export] archive error:', err);
|
|
res.destroy(err);
|
|
});
|
|
archive.pipe(res);
|
|
for (const f of files) archive.append(f.buffer, { name: f.name });
|
|
archive.append(JSON.stringify(report, null, 2), { name: 'export-report.json' });
|
|
await archive.finalize();
|
|
}));
|
|
|
|
// ------------------------------ Admin --------------------------------------
|
|
app.get('/api/admin/users', requireAuth, requireAdmin, (req, res) => {
|
|
const users = db.prepare(`
|
|
SELECT u.id, u.username, u.role, u.created_at,
|
|
(SELECT COUNT(*) FROM profiles p WHERE p.user_id = u.id) AS profileCount
|
|
FROM users u ORDER BY u.id`).all();
|
|
res.json({
|
|
users: users.map(u => ({
|
|
id: u.id, username: u.username, role: u.role, createdAt: u.created_at, profileCount: u.profileCount,
|
|
})),
|
|
});
|
|
});
|
|
|
|
app.put('/api/admin/users/:id/role', requireAuth, requireAdmin, (req, res) => {
|
|
const target = db.prepare('SELECT * FROM users WHERE id = ?').get(req.params.id);
|
|
if (!target) return res.status(404).json({ error: 'User not found.' });
|
|
const role = req.body?.role;
|
|
if (!['admin', 'user'].includes(role)) return res.status(400).json({ error: 'Role must be "admin" or "user".' });
|
|
if (target.id === req.user.id) return res.status(400).json({ error: 'You cannot change your own role.' });
|
|
if (target.role === 'admin' && role === 'user') {
|
|
const admins = db.prepare("SELECT COUNT(*) AS c FROM users WHERE role = 'admin'").get().c;
|
|
if (admins <= 1) return res.status(400).json({ error: 'Cannot demote the last admin.' });
|
|
}
|
|
db.prepare('UPDATE users SET role = ? WHERE id = ?').run(role, target.id);
|
|
res.json({ ok: true });
|
|
});
|
|
|
|
app.post('/api/admin/clone-profiles', requireAuth, requireAdmin, (req, res) => {
|
|
const from = db.prepare('SELECT * FROM users WHERE id = ?').get(req.body?.fromUserId);
|
|
const to = db.prepare('SELECT * FROM users WHERE id = ?').get(req.body?.toUserId);
|
|
if (!from || !to) return res.status(404).json({ error: 'User not found.' });
|
|
if (from.id === to.id) return res.status(400).json({ error: 'Source and target user must differ.' });
|
|
const rows = db.prepare('SELECT * FROM profiles WHERE user_id = ?').all(from.id);
|
|
if (rows.length === 0) return res.status(400).json({ error: `User "${from.username}" has no profiles to copy.` });
|
|
const insert = db.prepare(`
|
|
INSERT INTO profiles (user_id, name, host, port, db_name, db_user, db_pass_enc)
|
|
VALUES (?,?,?,?,?,?,?)`);
|
|
const tx = db.transaction(() => {
|
|
for (const p of rows) insert.run(to.id, p.name, p.host, p.port, p.db_name, p.db_user, p.db_pass_enc);
|
|
});
|
|
tx();
|
|
res.json({ ok: true, cloned: rows.length });
|
|
});
|
|
|
|
// ------------------------------ Misc ---------------------------------------
|
|
app.get('/api/health', (req, res) => {
|
|
const users = db.prepare('SELECT COUNT(*) AS c FROM users').get().c;
|
|
res.json({ ok: true, users });
|
|
});
|
|
|
|
app.use('/api', (req, res) => res.status(404).json({ error: 'Not found.' }));
|
|
|
|
// SPA fallback.
|
|
app.get('*', (req, res) => res.sendFile(path.join(__dirname, 'public', 'index.html')));
|
|
|
|
// Central error handler.
|
|
// eslint-disable-next-line no-unused-vars
|
|
app.use((err, req, res, next) => {
|
|
console.error('[error]', err);
|
|
if (res.headersSent) return;
|
|
res.status(err.status || 500).json({ error: err.expose ? err.message : 'Internal server error.' });
|
|
});
|
|
|
|
app.listen(PORT, () => {
|
|
console.log(`rdj-art-app listening on http://0.0.0.0:${PORT}`);
|
|
console.log(`Data directory: ${DATA_DIR}`);
|
|
});
|