421 lines
14 KiB
JavaScript
421 lines
14 KiB
JavaScript
import { DatabaseSync } from "node:sqlite";
|
|
import * as fs from "node:fs";
|
|
import * as path from "node:path";
|
|
import { DEFAULT_TOPICS, SEED_VIDEOS } from "./seed_data.js";
|
|
|
|
export function createDatabase(dbPath) {
|
|
const dir = path.dirname(dbPath);
|
|
if (!fs.existsSync(dir)) {
|
|
fs.mkdirSync(dir, { recursive: true });
|
|
}
|
|
|
|
const db = new DatabaseSync(dbPath);
|
|
|
|
// Initialize schema
|
|
db.exec(`
|
|
CREATE TABLE IF NOT EXISTS topics (
|
|
id TEXT PRIMARY KEY,
|
|
name TEXT NOT NULL,
|
|
type TEXT NOT NULL DEFAULT 'topic',
|
|
query TEXT NOT NULL,
|
|
active INTEGER DEFAULT 1,
|
|
weight REAL DEFAULT 1.0,
|
|
created_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS videos (
|
|
id TEXT PRIMARY KEY,
|
|
title TEXT NOT NULL,
|
|
channel TEXT NOT NULL,
|
|
channel_id TEXT,
|
|
thumbnail TEXT,
|
|
duration TEXT,
|
|
views TEXT,
|
|
published_at TEXT,
|
|
published_relative TEXT,
|
|
topic_id TEXT,
|
|
description TEXT,
|
|
url TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS seen_videos (
|
|
video_id TEXT PRIMARY KEY,
|
|
seen_at TEXT NOT NULL,
|
|
batch_id TEXT
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS feedback (
|
|
video_id TEXT PRIMARY KEY,
|
|
channel TEXT,
|
|
topic_id TEXT,
|
|
action TEXT NOT NULL,
|
|
created_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS nopes (
|
|
id TEXT PRIMARY KEY,
|
|
type TEXT NOT NULL,
|
|
target TEXT NOT NULL,
|
|
reason TEXT,
|
|
created_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS watchlist (
|
|
video_id TEXT PRIMARY KEY,
|
|
title TEXT NOT NULL,
|
|
channel TEXT NOT NULL,
|
|
thumbnail TEXT,
|
|
duration TEXT,
|
|
url TEXT NOT NULL,
|
|
topic_id TEXT,
|
|
watched INTEGER DEFAULT 0,
|
|
notes TEXT,
|
|
added_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS settings (
|
|
key TEXT PRIMARY KEY,
|
|
value TEXT NOT NULL
|
|
);
|
|
`);
|
|
|
|
// Populate default settings if missing
|
|
const getSettingStmt = db.prepare("SELECT value FROM settings WHERE key = ?");
|
|
const setSettingStmt = db.prepare("INSERT OR REPLACE INTO settings (key, value) VALUES (?, ?)");
|
|
|
|
if (!getSettingStmt.get("recency_bias")) {
|
|
setSettingStmt.run("recency_bias", "recent"); // recent, fresh, ultra_fresh, broad, any
|
|
}
|
|
if (!getSettingStmt.get("batch_size")) {
|
|
setSettingStmt.run("batch_size", "12");
|
|
}
|
|
|
|
// Populate default topics if empty
|
|
const countTopics = db.prepare("SELECT COUNT(*) AS count FROM topics").get();
|
|
if (countTopics.count === 0) {
|
|
const insertTopic = db.prepare(`
|
|
INSERT INTO topics (id, name, type, query, active, weight, created_at)
|
|
VALUES (?, ?, ?, ?, ?, ?, ?)
|
|
`);
|
|
const now = new Date().toISOString();
|
|
for (const t of DEFAULT_TOPICS) {
|
|
insertTopic.run(t.id, t.name, t.type, t.query, t.active, t.weight, now);
|
|
}
|
|
}
|
|
|
|
// Populate seed videos if empty
|
|
const countVideos = db.prepare("SELECT COUNT(*) AS count FROM videos").get();
|
|
if (countVideos.count === 0) {
|
|
const insertVideo = db.prepare(`
|
|
INSERT OR REPLACE INTO videos (id, title, channel, channel_id, thumbnail, duration, views, published_at, published_relative, topic_id, description, url)
|
|
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
|
|
`);
|
|
for (const v of SEED_VIDEOS) {
|
|
insertVideo.run(
|
|
v.id,
|
|
v.title,
|
|
v.channel,
|
|
v.channel_id || "",
|
|
v.thumbnail || `https://i.ytimg.com/vi/${v.id}/hqdefault.jpg`,
|
|
v.duration || "",
|
|
v.views || "",
|
|
v.published_at || new Date().toISOString(),
|
|
v.published_relative || "recent",
|
|
v.topic_id || "homelab-selfhost",
|
|
v.description || "",
|
|
v.url || `https://www.youtube.com/watch?v=${v.id}`
|
|
);
|
|
}
|
|
}
|
|
|
|
return {
|
|
rawDb: db,
|
|
|
|
// Topics API
|
|
getTopics() {
|
|
return db.prepare("SELECT * FROM topics ORDER BY weight DESC, name ASC").all();
|
|
},
|
|
|
|
getActiveTopics() {
|
|
return db.prepare("SELECT * FROM topics WHERE active = 1 ORDER BY weight DESC").all();
|
|
},
|
|
|
|
getTopicById(id) {
|
|
return db.prepare("SELECT * FROM topics WHERE id = ?").get(id);
|
|
},
|
|
|
|
addTopic(topic) {
|
|
const now = new Date().toISOString();
|
|
const id = topic.id || "topic-" + Date.now() + "-" + Math.random().toString(36).slice(2, 6);
|
|
const stmt = db.prepare(`
|
|
INSERT INTO topics (id, name, type, query, active, weight, created_at)
|
|
VALUES (?, ?, ?, ?, ?, ?, ?)
|
|
`);
|
|
stmt.run(
|
|
id,
|
|
topic.name,
|
|
topic.type || "topic",
|
|
topic.query || topic.name,
|
|
topic.active ?? 1,
|
|
topic.weight ?? 1.0,
|
|
now
|
|
);
|
|
return this.getTopicById(id);
|
|
},
|
|
|
|
updateTopic(id, updates) {
|
|
const topic = this.getTopicById(id);
|
|
if (!topic) return null;
|
|
|
|
const name = updates.name !== undefined ? updates.name : topic.name;
|
|
const type = updates.type !== undefined ? updates.type : topic.type;
|
|
const query = updates.query !== undefined ? updates.query : topic.query;
|
|
const active = updates.active !== undefined ? (updates.active ? 1 : 0) : topic.active;
|
|
const weight = updates.weight !== undefined ? Number(updates.weight) : topic.weight;
|
|
|
|
db.prepare(`
|
|
UPDATE topics
|
|
SET name = ?, type = ?, query = ?, active = ?, weight = ?
|
|
WHERE id = ?
|
|
`).run(name, type, query, active, weight, id);
|
|
|
|
return this.getTopicById(id);
|
|
},
|
|
|
|
deleteTopic(id) {
|
|
const info = db.prepare("DELETE FROM topics WHERE id = ?").run(id);
|
|
return info.changes > 0;
|
|
},
|
|
|
|
// Videos
|
|
upsertVideo(v) {
|
|
const stmt = db.prepare(`
|
|
INSERT INTO videos (id, title, channel, channel_id, thumbnail, duration, views, published_at, published_relative, topic_id, description, url)
|
|
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
|
|
ON CONFLICT(id) DO UPDATE SET
|
|
title = excluded.title,
|
|
channel = excluded.channel,
|
|
channel_id = COALESCE(excluded.channel_id, videos.channel_id),
|
|
thumbnail = excluded.thumbnail,
|
|
duration = excluded.duration,
|
|
views = excluded.views,
|
|
published_at = COALESCE(excluded.published_at, videos.published_at),
|
|
published_relative = COALESCE(excluded.published_relative, videos.published_relative),
|
|
topic_id = COALESCE(excluded.topic_id, videos.topic_id),
|
|
description = COALESCE(excluded.description, videos.description),
|
|
url = excluded.url
|
|
`);
|
|
stmt.run(
|
|
v.id,
|
|
v.title,
|
|
v.channel,
|
|
v.channel_id || "",
|
|
v.thumbnail || `https://i.ytimg.com/vi/${v.id}/hqdefault.jpg`,
|
|
v.duration || "",
|
|
v.views || "",
|
|
v.published_at || new Date().toISOString(),
|
|
v.published_relative || "recent",
|
|
v.topic_id || "",
|
|
v.description || "",
|
|
v.url || `https://www.youtube.com/watch?v=${v.id}`
|
|
);
|
|
},
|
|
|
|
getVideoById(id) {
|
|
return db.prepare("SELECT * FROM videos WHERE id = ?").get(id);
|
|
},
|
|
|
|
getAllVideos() {
|
|
return db.prepare("SELECT * FROM videos").all();
|
|
},
|
|
|
|
// Seen tracking (No Repeats)
|
|
recordSeenVideos(videoIds, batchId = null) {
|
|
const now = new Date().toISOString();
|
|
const stmt = db.prepare(`
|
|
INSERT OR REPLACE INTO seen_videos (video_id, seen_at, batch_id)
|
|
VALUES (?, ?, ?)
|
|
`);
|
|
for (const id of videoIds) {
|
|
stmt.run(id, now, batchId);
|
|
}
|
|
},
|
|
|
|
getSeenVideoIds() {
|
|
const rows = db.prepare("SELECT video_id FROM seen_videos").all();
|
|
return new Set(rows.map(r => r.video_id));
|
|
},
|
|
|
|
getSeenCount() {
|
|
const res = db.prepare("SELECT COUNT(*) as count FROM seen_videos").get();
|
|
return res.count;
|
|
},
|
|
|
|
clearSeenHistory() {
|
|
db.prepare("DELETE FROM seen_videos").run();
|
|
return true;
|
|
},
|
|
|
|
// Feedback & Steering
|
|
recordFeedback(videoId, action, meta = {}) {
|
|
const now = new Date().toISOString();
|
|
const video = this.getVideoById(videoId) || {};
|
|
const channel = meta.channel || video.channel || "";
|
|
const topicId = meta.topic_id || video.topic_id || "";
|
|
|
|
// Save feedback
|
|
db.prepare(`
|
|
INSERT OR REPLACE INTO feedback (video_id, channel, topic_id, action, created_at)
|
|
VALUES (?, ?, ?, ?, ?)
|
|
`).run(videoId, channel, topicId, action, now);
|
|
|
|
// Apply steering adjustments to topics
|
|
if (topicId) {
|
|
const topic = this.getTopicById(topicId);
|
|
if (topic) {
|
|
let delta = 0;
|
|
if (action === "thumbs_up") delta = 0.25;
|
|
if (action === "thumbs_down") delta = -0.2;
|
|
if (action === "big_nope") delta = -0.4;
|
|
const newWeight = Math.max(0.1, Math.min(3.0, (topic.weight || 1.0) + delta));
|
|
db.prepare("UPDATE topics SET weight = ? WHERE id = ?").run(newWeight, topicId);
|
|
}
|
|
}
|
|
|
|
// If Big Nope, also automatically add channel & video to nopes table
|
|
if (action === "big_nope") {
|
|
if (channel) {
|
|
this.addNope("channel", channel, `Big Nope on video: ${videoId}`);
|
|
}
|
|
this.addNope("video", videoId, `Direct Big Nope on video`);
|
|
}
|
|
|
|
return { success: true, action, videoId, channel, topicId };
|
|
},
|
|
|
|
getFeedback() {
|
|
return db.prepare("SELECT * FROM feedback ORDER BY created_at DESC").all();
|
|
},
|
|
|
|
getFeedbackSummary() {
|
|
const up = db.prepare("SELECT COUNT(*) as count FROM feedback WHERE action = 'thumbs_up'").get().count;
|
|
const down = db.prepare("SELECT COUNT(*) as count FROM feedback WHERE action = 'thumbs_down'").get().count;
|
|
const nope = db.prepare("SELECT COUNT(*) as count FROM feedback WHERE action = 'big_nope'").get().count;
|
|
return { thumbsUp: up, thumbsDown: down, bigNope: nope };
|
|
},
|
|
|
|
// Nopes (Blocklist)
|
|
getNopes() {
|
|
return db.prepare("SELECT * FROM nopes ORDER BY created_at DESC").all();
|
|
},
|
|
|
|
isNoped(videoId, channel = "", title = "") {
|
|
const videoNope = db.prepare("SELECT 1 FROM nopes WHERE type = 'video' AND target = ?").get(videoId);
|
|
if (videoNope) return true;
|
|
|
|
if (channel) {
|
|
const channelNope = db.prepare("SELECT 1 FROM nopes WHERE type = 'channel' AND LOWER(target) = LOWER(?)").get(channel);
|
|
if (channelNope) return true;
|
|
}
|
|
|
|
// Check keyword nopes
|
|
const keywordNopes = db.prepare("SELECT target FROM nopes WHERE type = 'keyword'").all();
|
|
const lowerTitle = (title || "").toLowerCase();
|
|
for (const k of keywordNopes) {
|
|
if (lowerTitle.includes(k.target.toLowerCase())) return true;
|
|
}
|
|
|
|
return false;
|
|
},
|
|
|
|
addNope(type, target, reason = "") {
|
|
const id = "nope-" + Date.now() + "-" + Math.random().toString(36).slice(2, 6);
|
|
const now = new Date().toISOString();
|
|
const existing = db.prepare("SELECT id FROM nopes WHERE type = ? AND LOWER(target) = LOWER(?)").get(type, target);
|
|
if (existing) return existing;
|
|
|
|
db.prepare(`
|
|
INSERT INTO nopes (id, type, target, reason, created_at)
|
|
VALUES (?, ?, ?, ?, ?)
|
|
`).run(id, type, target, reason, now);
|
|
|
|
return { id, type, target, reason, created_at: now };
|
|
},
|
|
|
|
removeNope(id) {
|
|
const info = db.prepare("DELETE FROM nopes WHERE id = ?").run(id);
|
|
return info.changes > 0;
|
|
},
|
|
|
|
// Watchlist API (Independent & Preserved across regeneration)
|
|
getWatchlist(filter = "all") {
|
|
let query = "SELECT * FROM watchlist";
|
|
if (filter === "unwatched") {
|
|
query += " WHERE watched = 0";
|
|
} else if (filter === "watched") {
|
|
query += " WHERE watched = 1";
|
|
}
|
|
query += " ORDER BY added_at DESC";
|
|
return db.prepare(query).all();
|
|
},
|
|
|
|
getWatchlistIds() {
|
|
const rows = db.prepare("SELECT video_id FROM watchlist").all();
|
|
return new Set(rows.map(r => r.video_id));
|
|
},
|
|
|
|
addToWatchlist(video) {
|
|
const now = new Date().toISOString();
|
|
db.prepare(`
|
|
INSERT INTO watchlist (video_id, title, channel, thumbnail, duration, url, topic_id, watched, notes, added_at)
|
|
VALUES (?, ?, ?, ?, ?, ?, ?, 0, ?, ?)
|
|
ON CONFLICT(video_id) DO UPDATE SET
|
|
title = excluded.title,
|
|
channel = excluded.channel,
|
|
thumbnail = excluded.thumbnail,
|
|
duration = excluded.duration,
|
|
url = excluded.url,
|
|
topic_id = COALESCE(excluded.topic_id, watchlist.topic_id)
|
|
`).run(
|
|
video.id || video.video_id,
|
|
video.title,
|
|
video.channel,
|
|
video.thumbnail || `https://i.ytimg.com/vi/${video.id || video.video_id}/hqdefault.jpg`,
|
|
video.duration || "",
|
|
video.url || `https://www.youtube.com/watch?v=${video.id || video.video_id}`,
|
|
video.topic_id || "",
|
|
video.notes || "",
|
|
now
|
|
);
|
|
|
|
return db.prepare("SELECT * FROM watchlist WHERE video_id = ?").get(video.id || video.video_id);
|
|
},
|
|
|
|
removeFromWatchlist(videoId) {
|
|
const info = db.prepare("DELETE FROM watchlist WHERE video_id = ?").run(videoId);
|
|
return info.changes > 0;
|
|
},
|
|
|
|
toggleWatchlistWatched(videoId) {
|
|
const item = db.prepare("SELECT watched FROM watchlist WHERE video_id = ?").get(videoId);
|
|
if (!item) return null;
|
|
const nextState = item.watched === 1 ? 0 : 1;
|
|
db.prepare("UPDATE watchlist SET watched = ? WHERE video_id = ?").run(nextState, videoId);
|
|
return db.prepare("SELECT * FROM watchlist WHERE video_id = ?").get(videoId);
|
|
},
|
|
|
|
// Settings
|
|
getSettings() {
|
|
const rows = db.prepare("SELECT key, value FROM settings").all();
|
|
const settings = {};
|
|
for (const r of rows) settings[r.key] = r.value;
|
|
return settings;
|
|
},
|
|
|
|
setSetting(key, value) {
|
|
db.prepare("INSERT OR REPLACE INTO settings (key, value) VALUES (?, ?)").run(key, String(value));
|
|
return { key, value };
|
|
}
|
|
};
|
|
}
|