import { all, get, run } from './db.js';
export const CATEGORIES = [
'All', 'Music', 'Gaming', 'Learning', 'Tech', 'Cooking',
'Travel', 'Fitness', 'Comedy', 'News', 'Nature', 'General',
];
export function publicUser(row, viewerId = null) {
if (!row) return null;
return {
id: row.id,
username: row.username,
displayName: row.display_name,
bio: row.bio ?? '',
avatarHue: row.avatar_hue,
createdAt: row.created_at,
subscriberCount: countSubscribers(row.id),
videoCount: get('SELECT COUNT(*) AS n FROM videos WHERE user_id = ?', row.id).n,
isSubscribed: viewerId ? isSubscribed(row.id, viewerId) : false,
isSelf: viewerId === row.id,
};
}
export const countSubscribers = (channelId) =>
get('SELECT COUNT(*) AS n FROM subscriptions WHERE channel_id = ?', channelId).n;
export const isSubscribed = (channelId, subscriberId) =>
!!get('SELECT 1 AS x FROM subscriptions WHERE channel_id = ? AND subscriber_id = ?', channelId, subscriberId);
const VIDEO_SELECT = `
SELECT v.*,
u.username, u.display_name, u.avatar_hue,
(SELECT COUNT(*) FROM video_likes l WHERE l.video_id = v.id AND l.value = 1) AS likes,
(SELECT COUNT(*) FROM video_likes l WHERE l.video_id = v.id AND l.value = -1) AS dislikes,
(SELECT COUNT(*) FROM comments c WHERE c.video_id = v.id) AS comment_count,
(SELECT COUNT(*) FROM subscriptions s WHERE s.channel_id = v.user_id) AS subscriber_count,
(SELECT l.value FROM video_likes l WHERE l.video_id = v.id AND l.user_id = ?) AS my_vote,
(SELECT 1 FROM subscriptions s WHERE s.channel_id = v.user_id AND s.subscriber_id = ?) AS subscribed,
(SELECT 1 FROM watch_later w WHERE w.video_id = v.id AND w.user_id = ?) AS saved
FROM videos v
JOIN users u ON u.id = v.user_id`;
export function shapeVideo(row, viewerId = null) {
if (!row) return null;
return {
id: row.id,
title: row.title,
description: row.description,
category: row.category,
src: row.src,
thumb: row.thumb,
duration: row.duration,
views: row.views,
createdAt: row.created_at,
likes: row.likes ?? 0,
dislikes: row.dislikes ?? 0,
commentCount: row.comment_count ?? 0,
myVote: row.my_vote ?? 0,
saved: !!row.saved,
isOwner: viewerId === row.user_id,
channel: {
id: row.user_id,
username: row.username,
displayName: row.display_name,
avatarHue: row.avatar_hue,
subscriberCount: row.subscriber_count ?? 0,
isSubscribed: !!row.subscribed,
isSelf: viewerId === row.user_id,
},
};
}
export function findVideo(id, viewerId = null) {
const v = viewerId ?? 0;
return shapeVideo(get(`${VIDEO_SELECT} WHERE v.id = ?`, v, v, v, id), viewerId);
}
const SORTS = {
new: 'v.created_at DESC, v.id DESC',
popular: 'v.views DESC, v.created_at DESC',
liked: 'likes DESC, v.views DESC',
oldest: 'v.created_at ASC',
};
export function listVideos({
viewerId = null, channelId = null, category = null, q = null,
sort = 'new', limit = 24, offset = 0, subscribedBy = null, excludeId = null,
} = {}) {
const v = viewerId ?? 0;
const where = [];
const params = [v, v, v];
if (channelId) { where.push('v.user_id = ?'); params.push(channelId); }
if (category && category !== 'All') { where.push('v.category = ?'); params.push(category); }
if (excludeId) { where.push('v.id <> ?'); params.push(excludeId); }
if (subscribedBy) {
where.push('v.user_id IN (SELECT channel_id FROM subscriptions WHERE subscriber_id = ?)');
params.push(subscribedBy);
}
if (q) {
where.push('(v.title LIKE ? OR v.description LIKE ? OR u.display_name LIKE ? OR u.username LIKE ?)');
const like = `%${q.replace(/[%_]/g, (m) => `\\${m}`)}%`;
params.push(like, like, like, like);
}
const sql = `${VIDEO_SELECT}
${where.length ? `WHERE ${where.join(' AND ')}` : ''}
ORDER BY ${SORTS[sort] ?? SORTS.new}
LIMIT ? OFFSET ?`;
params.push(limit, offset);
return all(sql, ...params).map((row) => shapeVideo(row, viewerId));
}
export function countVideos({ channelId = null, category = null, q = null, subscribedBy = null } = {}) {
const where = [];
const params = [];
if (channelId) { where.push('v.user_id = ?'); params.push(channelId); }
if (category && category !== 'All') { where.push('v.category = ?'); params.push(category); }
if (subscribedBy) {
where.push('v.user_id IN (SELECT channel_id FROM subscriptions WHERE subscriber_id = ?)');
params.push(subscribedBy);
}
if (q) {
where.push('(v.title LIKE ? OR v.description LIKE ? OR u.display_name LIKE ? OR u.username LIKE ?)');
const like = `%${q.replace(/[%_]/g, (m) => `\\${m}`)}%`;
params.push(like, like, like, like);
}
const sql = `SELECT COUNT(*) AS n FROM videos v JOIN users u ON u.id = v.user_id
${where.length ? `WHERE ${where.join(' AND ')}` : ''}`;
return get(sql, ...params).n;
}
/** Videos the viewer liked, newest like first. */
export function likedVideos(viewerId, limit = 60) {
return all(
`${VIDEO_SELECT}
JOIN video_likes ml ON ml.video_id = v.id AND ml.user_id = ? AND ml.value = 1
ORDER BY ml.created_at DESC LIMIT ?`,
viewerId, viewerId, viewerId, viewerId, limit
).map((row) => shapeVideo(row, viewerId));
}
export function historyVideos(viewerId, limit = 60) {
return all(
`${VIDEO_SELECT}
JOIN watch_history h ON h.video_id = v.id AND h.user_id = ?
ORDER BY h.watched_at DESC LIMIT ?`,
viewerId, viewerId, viewerId, viewerId, limit
).map((row) => shapeVideo(row, viewerId));
}
export function watchLaterVideos(viewerId, limit = 60) {
return all(
`${VIDEO_SELECT}
JOIN watch_later w ON w.video_id = v.id AND w.user_id = ?
ORDER BY w.added_at DESC LIMIT ?`,
viewerId, viewerId, viewerId, viewerId, limit
).map((row) => shapeVideo(row, viewerId));
}
/** Related videos: same category first, then anything else recent. */
export function relatedVideos(video, viewerId, limit = 12) {
const same = listVideos({ viewerId, category: video.category, excludeId: video.id, sort: 'popular', limit });
if (same.length >= limit) return same;
const seen = new Set(same.map((x) => x.id));
const filler = listVideos({ viewerId, excludeId: video.id, sort: 'popular', limit: limit * 2 })
.filter((x) => !seen.has(x.id))
.slice(0, limit - same.length);
return [...same, ...filler];
}
const COMMENT_SELECT = `
SELECT c.*, u.username, u.display_name, u.avatar_hue,
(SELECT COUNT(*) FROM comment_likes cl WHERE cl.comment_id = c.id AND cl.value = 1) AS likes,
(SELECT cl.value FROM comment_likes cl WHERE cl.comment_id = c.id AND cl.user_id = ?) AS my_vote,
(SELECT COUNT(*) FROM comments r WHERE r.parent_id = c.id) AS reply_count
FROM comments c JOIN users u ON u.id = c.user_id`;
const shapeComment = (row, viewerId, ownerId) => ({
id: row.id,
videoId: row.video_id,
parentId: row.parent_id,
body: row.body,
createdAt: row.created_at,
likes: row.likes ?? 0,
myVote: row.my_vote ?? 0,
replyCount: row.reply_count ?? 0,
canDelete: viewerId != null && (viewerId === row.user_id || viewerId === ownerId),
isCreator: row.user_id === ownerId,
author: {
id: row.user_id,
username: row.username,
displayName: row.display_name,
avatarHue: row.avatar_hue,
},
replies: [],
});
/** Top-level comments for a video with their replies nested one level deep. */
export function videoComments(videoId, viewerId, sort = 'new') {
const v = viewerId ?? 0;
const ownerId = get('SELECT user_id FROM videos WHERE id = ?', videoId)?.user_id ?? null;
const order = sort === 'top' ? 'likes DESC, c.created_at DESC' : 'c.created_at DESC';
const tops = all(
`${COMMENT_SELECT} WHERE c.video_id = ? AND c.parent_id IS NULL ORDER BY ${order}`,
v, videoId
).map((row) => shapeComment(row, viewerId, ownerId));
if (!tops.length) return tops;
const byId = new Map(tops.map((c) => [c.id, c]));
const replies = all(
`${COMMENT_SELECT} WHERE c.video_id = ? AND c.parent_id IS NOT NULL ORDER BY c.created_at ASC`,
v, videoId
);
for (const row of replies) {
byId.get(row.parent_id)?.replies.push(shapeComment(row, viewerId, ownerId));
}
return tops;
}
export function findComment(id, viewerId) {
const row = get(`${COMMENT_SELECT} WHERE c.id = ?`, viewerId ?? 0, id);
if (!row) return null;
const ownerId = get('SELECT user_id FROM videos WHERE id = ?', row.video_id)?.user_id ?? null;
return shapeComment(row, viewerId, ownerId);
}
/** Record a view, deduped per user per session-ish window by the caller. */
export function recordView(videoId, viewerId) {
run('UPDATE videos SET views = views + 1 WHERE id = ?', videoId);
if (viewerId) {
run(
`INSERT INTO watch_history (user_id, video_id) VALUES (?, ?)
ON CONFLICT(user_id, video_id) DO UPDATE SET watched_at = datetime('now')`,
viewerId, videoId
);
}
}