307 lines
11 KiB
JavaScript
307 lines
11 KiB
JavaScript
const { Pool } = require('pg');
|
|
const fs = require('fs');
|
|
const path = require('path');
|
|
const { importCatalogIfEmpty } = require('./importer');
|
|
|
|
let pool = null;
|
|
let isConnected = false;
|
|
|
|
function getPool() {
|
|
if (pool) return pool;
|
|
|
|
let config;
|
|
if (process.env.DATABASE_URL) {
|
|
const match = process.env.DATABASE_URL.match(/^postgres(?:ql)?:\/\/([^:]+):(.*)@([^:/]+)(?::(\d+))?\/(.+)$/);
|
|
if (match) {
|
|
config = {
|
|
user: match[1],
|
|
password: match[2],
|
|
host: match[3],
|
|
port: match[4] ? parseInt(match[4], 10) : 5432,
|
|
database: match[5]
|
|
};
|
|
} else {
|
|
config = { connectionString: process.env.DATABASE_URL };
|
|
}
|
|
} else {
|
|
config = {
|
|
host: process.env.DB_HOST || 'localhost',
|
|
port: parseInt(process.env.DB_PORT || '5432', 10),
|
|
user: process.env.DB_USER || 'postgres',
|
|
password: process.env.DB_PASSWORD || 'postgres',
|
|
database: process.env.DB_NAME || 'keyselector',
|
|
};
|
|
}
|
|
|
|
pool = new Pool(config);
|
|
|
|
pool.on('error', (err) => {
|
|
console.error('[PostgreSQL] Unexpected pool error:', err.message);
|
|
});
|
|
|
|
return pool;
|
|
}
|
|
|
|
async function initDb() {
|
|
try {
|
|
const p = getPool();
|
|
const client = await p.connect();
|
|
isConnected = true;
|
|
console.log('[PostgreSQL] Successfully connected to database.');
|
|
|
|
const schemaPath = path.join(__dirname, '../db/schema.sql');
|
|
if (fs.existsSync(schemaPath)) {
|
|
const sql = fs.readFileSync(schemaPath, 'utf8');
|
|
await client.query(sql);
|
|
console.log('[PostgreSQL] Schema and seed data verified/applied.');
|
|
}
|
|
|
|
// Automatically seed full 26,000+ track catalog if DB is empty or below threshold
|
|
await importCatalogIfEmpty(client);
|
|
|
|
client.release();
|
|
} catch (err) {
|
|
isConnected = false;
|
|
console.warn('[PostgreSQL] Database connection failed:', err.message);
|
|
console.warn('[PostgreSQL] Running with in-memory fallback until DB is available.');
|
|
}
|
|
}
|
|
|
|
// In-memory fallback if Postgres is temporarily down
|
|
const memoryTracks = new Map();
|
|
|
|
async function query(text, params = []) {
|
|
if (!isConnected) {
|
|
return { rows: [] };
|
|
}
|
|
return getPool().query(text, params);
|
|
}
|
|
|
|
function normalizeTrackTitle(str) {
|
|
if (!str) return '';
|
|
return str
|
|
.toLowerCase()
|
|
.replace(/\(.*?\)|\[.*?\]/g, '') // strip (feat. ...), [feat. ...]
|
|
.replace(/ - .*$/g, '') // strip trailing - Radio Edit, etc.
|
|
.replace(/[^a-z0-9]/g, ' ')
|
|
.replace(/\s+/g, ' ')
|
|
.trim();
|
|
}
|
|
|
|
function normalizeTrackArtist(str) {
|
|
if (!str) return '';
|
|
const first = str.split(/,|&|feat\.|with|vs\./i)[0];
|
|
return first
|
|
.toLowerCase()
|
|
.replace(/[^a-z0-9]/g, ' ')
|
|
.replace(/\s+/g, ' ')
|
|
.trim();
|
|
}
|
|
|
|
// Find track by artist and title (case insensitive, with fuzzy fallback)
|
|
async function findTrack(artist, title) {
|
|
if (!artist || !title) return null;
|
|
|
|
const cleanArt = artist.trim();
|
|
const cleanTitle = title.trim();
|
|
|
|
if (isConnected) {
|
|
try {
|
|
// 1. Exact match
|
|
const res = await getPool().query(
|
|
`SELECT * FROM tracks
|
|
WHERE LOWER(artist) = LOWER($1) AND LOWER(title) = LOWER($2)
|
|
LIMIT 1`,
|
|
[cleanArt, cleanTitle]
|
|
);
|
|
if (res.rows.length > 0) return res.rows[0];
|
|
|
|
// 2. Fuzzy match with stripped/normalized title & primary artist
|
|
const normTitle = normalizeTrackTitle(cleanTitle);
|
|
const normArt = normalizeTrackArtist(cleanArt);
|
|
|
|
if (normTitle.length >= 2 && normArt.length >= 2) {
|
|
const fuzzyRes = await getPool().query(
|
|
`SELECT * FROM tracks
|
|
WHERE (LOWER(artist) LIKE $1 OR LOWER(artist) LIKE $2)
|
|
AND (LOWER(title) LIKE $3 OR LOWER(title) LIKE $4)
|
|
ORDER BY is_corrected DESC, play_count DESC
|
|
LIMIT 1`,
|
|
[`%${normArt}%`, `${normArt}%`, `%${normTitle}%`, `${normTitle}%`]
|
|
);
|
|
if (fuzzyRes.rows.length > 0) return fuzzyRes.rows[0];
|
|
}
|
|
} catch (e) {
|
|
console.error('[DB] findTrack error:', e.message);
|
|
}
|
|
}
|
|
|
|
// Memory fallback
|
|
const exactKey = `${cleanArt.toLowerCase()} - ${cleanTitle.toLowerCase()}`;
|
|
if (memoryTracks.has(exactKey)) return memoryTracks.get(exactKey);
|
|
|
|
const normTitle = normalizeTrackTitle(cleanTitle);
|
|
const normArt = normalizeTrackArtist(cleanArt);
|
|
for (const track of memoryTracks.values()) {
|
|
const tNormTitle = normalizeTrackTitle(track.title);
|
|
const tNormArt = normalizeTrackArtist(track.artist);
|
|
if (tNormTitle.includes(normTitle) || normTitle.includes(tNormTitle)) {
|
|
if (tNormArt.includes(normArt) || normArt.includes(tNormArt)) {
|
|
return track;
|
|
}
|
|
}
|
|
}
|
|
|
|
return null;
|
|
}
|
|
|
|
// Save or update track
|
|
async function saveTrack({ title, artist, camelot_key, bpm, music_key, cover_url, preview_url, duration_sec, energy, danceability, happiness, tags = [], is_corrected = false, source = 'manual' }) {
|
|
if (!title || !artist || !camelot_key || !bpm) return null;
|
|
|
|
if (isConnected) {
|
|
try {
|
|
const res = await getPool().query(
|
|
`INSERT INTO tracks (title, artist, camelot_key, bpm, music_key, cover_url, preview_url, duration_sec, energy, danceability, happiness, tags, is_corrected, source, play_count, updated_at)
|
|
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, 1, CURRENT_TIMESTAMP)
|
|
ON CONFLICT (artist, title) DO UPDATE
|
|
SET play_count = tracks.play_count + 1,
|
|
cover_url = COALESCE(EXCLUDED.cover_url, tracks.cover_url),
|
|
preview_url = COALESCE(EXCLUDED.preview_url, tracks.preview_url),
|
|
duration_sec = COALESCE(EXCLUDED.duration_sec, tracks.duration_sec),
|
|
energy = COALESCE(EXCLUDED.energy, tracks.energy),
|
|
danceability = COALESCE(EXCLUDED.danceability, tracks.danceability),
|
|
happiness = COALESCE(EXCLUDED.happiness, tracks.happiness),
|
|
camelot_key = CASE WHEN tracks.is_corrected THEN tracks.camelot_key ELSE EXCLUDED.camelot_key END,
|
|
bpm = CASE WHEN tracks.is_corrected THEN tracks.bpm ELSE EXCLUDED.bpm END,
|
|
music_key = CASE WHEN tracks.is_corrected THEN tracks.music_key ELSE EXCLUDED.music_key END,
|
|
updated_at = CURRENT_TIMESTAMP
|
|
RETURNING *`,
|
|
[title.trim(), artist.trim(), camelot_key, Math.round(bpm), music_key, cover_url, preview_url, duration_sec || null, energy || null, danceability || null, happiness || null, tags, is_corrected, source]
|
|
);
|
|
return res.rows[0];
|
|
} catch (e) {
|
|
console.error('[DB] saveTrack error:', e.message);
|
|
}
|
|
}
|
|
|
|
const track = { title, artist, camelot_key, bpm: Math.round(bpm), music_key, cover_url, preview_url, duration_sec, energy, danceability, happiness, tags, is_corrected, source };
|
|
const key = `${artist.trim().toLowerCase()} - ${title.trim().toLowerCase()}`;
|
|
memoryTracks.set(key, track);
|
|
return track;
|
|
}
|
|
|
|
// Save explicit user correction
|
|
async function correctTrack({ artist, title, camelot_key, bpm, music_key }) {
|
|
if (!artist || !title || !camelot_key || !bpm) return null;
|
|
|
|
if (isConnected) {
|
|
try {
|
|
const res = await getPool().query(
|
|
`INSERT INTO tracks (title, artist, camelot_key, bpm, music_key, is_corrected, updated_at)
|
|
VALUES ($1, $2, $3, $4, $5, TRUE, CURRENT_TIMESTAMP)
|
|
ON CONFLICT (artist, title) DO UPDATE
|
|
SET camelot_key = EXCLUDED.camelot_key,
|
|
bpm = EXCLUDED.bpm,
|
|
music_key = EXCLUDED.music_key,
|
|
is_corrected = TRUE,
|
|
updated_at = CURRENT_TIMESTAMP
|
|
RETURNING *`,
|
|
[title.trim(), artist.trim(), camelot_key, Math.round(bpm), music_key]
|
|
);
|
|
return res.rows[0];
|
|
} catch (e) {
|
|
console.error('[DB] correctTrack error:', e.message);
|
|
}
|
|
}
|
|
|
|
const existing = await findTrack(artist, title) || { title, artist };
|
|
existing.camelot_key = camelot_key;
|
|
existing.bpm = Math.round(bpm);
|
|
existing.music_key = music_key;
|
|
existing.is_corrected = true;
|
|
const key = `${artist.trim().toLowerCase()} - ${title.trim().toLowerCase()}`;
|
|
memoryTracks.set(key, existing);
|
|
return existing;
|
|
}
|
|
|
|
// Get suggestions matching compatible camelot keys and BPM range
|
|
async function getSuggestions(compatibleKeys = [], minBpm = 80, maxBpm = 180, limit = 10, excludeArtist = '', excludeTitle = '') {
|
|
if (isConnected) {
|
|
try {
|
|
const res = await getPool().query(
|
|
`SELECT id, title, artist, camelot_key, bpm, music_key, cover_url, preview_url, duration_sec, energy, danceability, happiness, tags, play_count
|
|
FROM tracks
|
|
WHERE camelot_key = ANY($1)
|
|
AND bpm BETWEEN $2 AND $3
|
|
AND NOT (LOWER(artist) = LOWER($4) AND LOWER(title) = LOWER($5))
|
|
ORDER BY play_count DESC, id DESC
|
|
LIMIT $6`,
|
|
[compatibleKeys, Math.round(minBpm), Math.round(maxBpm), excludeArtist, excludeTitle, limit]
|
|
);
|
|
return res.rows;
|
|
} catch (e) {
|
|
console.error('[DB] getSuggestions error:', e.message);
|
|
}
|
|
}
|
|
|
|
// Fallback search in memory
|
|
const results = [];
|
|
for (const track of memoryTracks.values()) {
|
|
if (compatibleKeys.includes(track.camelot_key) && track.bpm >= minBpm && track.bpm <= maxBpm) {
|
|
if (excludeArtist && track.artist.toLowerCase() === excludeArtist.toLowerCase()) continue;
|
|
results.push(track);
|
|
if (results.length >= limit) break;
|
|
}
|
|
}
|
|
return results;
|
|
}
|
|
|
|
// Search tracks by query
|
|
async function searchTracks(queryStr, limit = 10) {
|
|
if (!queryStr) return [];
|
|
const term = `%${queryStr.trim().toLowerCase()}%`;
|
|
|
|
if (isConnected) {
|
|
try {
|
|
const res = await getPool().query(
|
|
`SELECT id, title, artist, camelot_key, bpm, music_key, cover_url, preview_url, duration_sec, energy, danceability, happiness, is_corrected
|
|
FROM tracks
|
|
WHERE LOWER(title) LIKE $1
|
|
OR LOWER(artist) LIKE $1
|
|
OR LOWER(artist || ' ' || title) LIKE $1
|
|
OR LOWER(title || ' ' || artist) LIKE $1
|
|
ORDER BY is_corrected DESC, play_count DESC
|
|
LIMIT $2`,
|
|
[term, limit]
|
|
);
|
|
return res.rows;
|
|
} catch (e) {
|
|
console.error('[DB] searchTracks error:', e.message);
|
|
}
|
|
}
|
|
|
|
const results = [];
|
|
const q = queryStr.toLowerCase();
|
|
for (const track of memoryTracks.values()) {
|
|
const combined1 = `${track.artist} ${track.title}`.toLowerCase();
|
|
const combined2 = `${track.title} ${track.artist}`.toLowerCase();
|
|
if (track.title.toLowerCase().includes(q) || track.artist.toLowerCase().includes(q) || combined1.includes(q) || combined2.includes(q)) {
|
|
results.push(track);
|
|
if (results.length >= limit) break;
|
|
}
|
|
}
|
|
return results;
|
|
}
|
|
|
|
module.exports = {
|
|
initDb,
|
|
query,
|
|
findTrack,
|
|
saveTrack,
|
|
correctTrack,
|
|
getSuggestions,
|
|
searchTracks,
|
|
isDbConnected: () => isConnected
|
|
};
|