admin/pinkudex
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
master
pinkudex/lib/db/queries.ts
T
admin 7e2c2ff89c Initial commit
2026-05-26 22:46:00 +02:00

1291 lines
49 KiB
TypeScript
Raw Permalink Blame History

import "server-only";
import { db, rawDb } from "./client";
import { images, collections } from "./schema";
import { eq, desc, sql, inArray, and, isNull } from "drizzle-orm";
import type { CardImage } from "@/components/grid/ImageCard";
export type SortKey = "newest" | "oldest" | "az" | "za" | "code-az" | "code-za";
const SORT_CLAUSE: Record<SortKey, string> = {
newest: "i.created_at DESC, i.id DESC",
oldest: "i.created_at ASC, i.id ASC",
az: "COALESCE(i.title, i.filename) COLLATE NOCASE ASC",
za: "COALESCE(i.title, i.filename) COLLATE NOCASE DESC",
"code-az": "i.code COLLATE NOCASE ASC",
"code-za": "i.code COLLATE NOCASE DESC",
};
const COVER_COLS = `
i.id, i.thumb_path, i.width, i.height,
i.code, i.title, i.rating, i.watched,
i.is_vip, i.is_favorite, i.is_owned,
i.has_video, i.has_subtitle,
s.name AS studio_name
`;
const COVER_BASE_JOIN = `LEFT JOIN studios s ON s.id = i.studio_id`;
interface CoverRow {
id: number; thumb_path: string; width: number; height: number;
code: string | null; title: string | null;
rating: number | null; watched: number;
is_vip: number; is_favorite: number; is_owned: number;
studio_name: string | null;
has_video: number; has_subtitle: number;
}
function rowToCard(r: CoverRow): CardImage {
return {
id: r.id,
thumbPath: r.thumb_path,
width: r.width,
height: r.height,
code: r.code,
title: r.title,
rating: r.rating,
watched: !!r.watched,
isVip: !!r.is_vip,
isFavorite: !!r.is_favorite,
isOwned: !!r.is_owned,
studioName: r.studio_name,
actresses: [],
hasVideo: !!r.has_video,
hasSubtitle: !!r.has_subtitle,
};
}
/**
* Attach actress info (id/name/slug) to a list of CardImage. One batched
* query for all ids; results grouped per image. Mutates the cards in
* place and returns them.
*/
function attachActresses(cards: CardImage[]): CardImage[] {
if (cards.length === 0) return cards;
const ids = cards.map((c) => c.id);
const placeholders = ids.map(() => "?").join(",");
const rows = rawDb.prepare(`
SELECT ia.image_id, a.id AS actress_id, a.name, a.slug
FROM image_actresses ia
JOIN actresses a ON a.id = ia.actress_id
WHERE ia.image_id IN (${placeholders})
ORDER BY a.name
`).all(...ids) as Array<{ image_id: number; actress_id: number; name: string; slug: string }>;
const byImage = new Map<number, CardImage["actresses"]>();
for (const r of rows) {
const arr = byImage.get(r.image_id) ?? [];
arr.push({ id: r.actress_id, name: r.name, slug: r.slug });
byImage.set(r.image_id, arr);
}
for (const c of cards) c.actresses = byImage.get(c.id) ?? [];
return cards;
}
/**
* Count covers grouped by code's first letter (A-Z, "#" for non-letter / no code).
* Total returned as the empty key "" for the "All" bucket.
*/
export function libraryLetterCounts(opts?: {
untagged?: boolean;
uncollected?: boolean;
unwatched?: boolean;
watched?: boolean;
rated?: boolean;
unrated?: boolean;
hasTags?: boolean;
hasCollection?: boolean;
hasVideo?: boolean;
noVideo?: boolean;
marks?: Array<"vip" | "favorite" | "owned" | "unmarked">;
actressId?: number;
// Multi-id filters — must match the same criteria the grid query uses,
// otherwise the letter counts and the grid will disagree on every
// filtered view.
actressIds?: number[];
actressMode?: FilterMode;
studioIds?: number[];
seriesIds?: number[];
genreIds?: number[];
genreMode?: FilterMode;
collectionIds?: number[];
collectionMode?: FilterMode;
tagIds?: number[];
tagMode?: FilterMode;
/** Tag-category filter. OR mode: cover has any tag whose category_id
* is in the selected list. AND mode: cover has at least one tag from
* EACH selected category. */
categoryIds?: number[];
categoryMode?: FilterMode;
search?: string;
}): Record<string, number> {
const where: string[] = ["i.deleted_at IS NULL", "i.parent_image_id IS NULL"];
const joins: string[] = [];
const params: Array<string | number> = [];
if (opts?.untagged) where.push("NOT EXISTS (SELECT 1 FROM image_tags WHERE image_id = i.id)");
if (opts?.uncollected) where.push("NOT EXISTS (SELECT 1 FROM collection_images WHERE image_id = i.id)");
if (opts?.unwatched) where.push("i.watched = 0");
if (opts?.watched) where.push("i.watched = 1");
if (opts?.rated) where.push("i.rating IS NOT NULL");
if (opts?.unrated) where.push("i.rating IS NULL");
if (opts?.hasTags) where.push("EXISTS (SELECT 1 FROM image_tags WHERE image_id = i.id)");
if (opts?.hasCollection) where.push("EXISTS (SELECT 1 FROM collection_images WHERE image_id = i.id)");
if (opts?.hasVideo) where.push("i.has_video = 1");
if (opts?.noVideo) where.push("i.has_video = 0");
if (opts?.marks && opts.marks.length > 0) {
// Multi-select marks are OR'd: a cover that is VIP or Owned matches "vip+owned".
const expr: Record<string, string> = {
vip: "i.is_vip = 1",
favorite: "i.is_favorite = 1",
owned: "i.is_owned = 1",
unmarked: "(i.is_vip = 0 AND i.is_favorite = 0)",
};
const parts = opts.marks.map((m) => expr[m]).filter(Boolean);
if (parts.length > 0) where.push(`(${parts.join(" OR ")})`);
}
if (opts?.actressId != null) {
joins.push("JOIN image_actresses ia ON ia.image_id = i.id");
where.push("ia.actress_id = ?");
params.push(opts.actressId);
}
const placeholders = (n: number) => Array(n).fill("?").join(",");
function applyMulti(ids: number[] | undefined, mode: FilterMode | undefined, tableExists: string, fkExists: string) {
if (!ids || ids.length === 0) return;
if (mode === "and") {
for (const id of ids) {
where.push(`EXISTS (SELECT 1 FROM ${tableExists} WHERE image_id = i.id AND ${fkExists} = ?)`);
params.push(id);
}
} else {
where.push(`EXISTS (SELECT 1 FROM ${tableExists} WHERE image_id = i.id AND ${fkExists} IN (${placeholders(ids.length)}))`);
params.push(...ids);
}
}
applyMulti(opts?.actressIds, opts?.actressMode, "image_actresses", "actress_id");
applyMulti(opts?.genreIds, opts?.genreMode, "image_genres", "genre_id");
applyMulti(opts?.tagIds, opts?.tagMode, "image_tags", "tag_id");
applyMulti(opts?.collectionIds, opts?.collectionMode, "collection_images", "collection_id");
applyCategoryFilter(where, params, opts?.categoryIds, opts?.categoryMode, placeholders);
if (opts?.studioIds && opts.studioIds.length > 0) {
where.push(`i.studio_id IN (${placeholders(opts.studioIds.length)})`);
params.push(...opts.studioIds);
}
if (opts?.seriesIds && opts.seriesIds.length > 0) {
where.push(`i.series_id IN (${placeholders(opts.seriesIds.length)})`);
params.push(...opts.seriesIds);
}
if (opts?.search) {
const q = opts.search.trim();
if (q) {
const like = `%${q.replace(/[\\%_]/g, (c) => `\\${c}`)}%`;
where.push("(COALESCE(i.code,'') LIKE ? ESCAPE '\\' OR COALESCE(i.title,'') LIKE ? ESCAPE '\\' OR COALESCE(i.notes,'') LIKE ? ESCAPE '\\')");
params.push(like, like, like);
}
}
const rows = rawDb.prepare(`
SELECT
CASE
WHEN i.code IS NULL THEN '#'
WHEN substr(upper(i.code), 1, 1) BETWEEN 'A' AND 'Z' THEN substr(upper(i.code), 1, 1)
ELSE '#'
END AS bucket,
COUNT(*) AS n
FROM images i
${joins.join("\n ")}
WHERE ${where.join(" AND ")}
GROUP BY bucket
`).all(...params) as Array<{ bucket: string; n: number }>;
const out: Record<string, number> = { "": 0 };
for (const r of rows) {
out[r.bucket] = r.n;
out[""] += r.n;
}
return out;
}
export type FilterMode = "or" | "and";
/** Translate a tag-category filter into SQL EXISTS clauses on the
* composed `where` and `params` arrays. OR mode: at least one tag of
* the cover lives in any selected category. AND mode: cover has at
* least one tag from EACH selected category. */
function applyCategoryFilter(
where: string[],
params: Array<string | number>,
ids: number[] | undefined,
mode: FilterMode | undefined,
placeholders: (n: number) => string,
) {
if (!ids || ids.length === 0) return;
if (mode === "and") {
for (const id of ids) {
where.push(`EXISTS (
SELECT 1 FROM image_tags it_c
JOIN tags t_c ON t_c.id = it_c.tag_id
WHERE it_c.image_id = i.id AND t_c.category_id = ?
)`);
params.push(id);
}
} else {
where.push(`EXISTS (
SELECT 1 FROM image_tags it_c
JOIN tags t_c ON t_c.id = it_c.tag_id
WHERE it_c.image_id = i.id AND t_c.category_id IN (${placeholders(ids.length)})
)`);
params.push(...ids);
}
}
export function listImages(opts?: {
limit?: number;
/** Skip this many rows before applying limit. Used for paginated UIs. */
offset?: number;
tagId?: number;
collectionId?: number;
studioId?: number;
labelId?: number;
seriesId?: number;
actressId?: number;
genreId?: number;
/** Multi-select equivalents. AND-mode requires the cover to match every id; OR-mode requires any. */
actressIds?: number[];
actressMode?: FilterMode;
studioIds?: number[];
seriesIds?: number[];
genreIds?: number[];
genreMode?: FilterMode;
collectionIds?: number[];
collectionMode?: FilterMode;
tagIds?: number[];
tagMode?: FilterMode;
/** Tag-category filter; expands to "any tag in this category" via EXISTS. */
categoryIds?: number[];
categoryMode?: FilterMode;
untagged?: boolean;
uncollected?: boolean;
unwatched?: boolean;
watched?: boolean;
rated?: boolean;
unrated?: boolean;
hasTags?: boolean;
hasCollection?: boolean;
hasVideo?: boolean;
noVideo?: boolean;
/** Mark filter: "vip" / "favorite" / "unmarked" — restrict by cover-level VIP/Favorite flag. */
marks?: Array<"vip" | "favorite" | "owned" | "unmarked">;
sort?: SortKey;
/** Filter by code's first letter (uppercase A-Z) or "#" for non-letter / no code. */
letter?: string;
/** Free-text substring match against code, title, and notes. */
search?: string;
}): CardImage[] {
const limit = opts?.limit ?? 200;
const joins: string[] = [COVER_BASE_JOIN];
const where: string[] = ["i.deleted_at IS NULL", "i.parent_image_id IS NULL"];
const params: Array<string | number> = [];
const sortKey: SortKey = opts?.sort ?? "newest";
let order = SORT_CLAUSE[sortKey];
if (opts?.tagId != null) {
joins.push("JOIN image_tags it ON it.image_id = i.id");
where.push("it.tag_id = ?");
params.push(opts.tagId);
}
if (opts?.actressId != null) {
joins.push("JOIN image_actresses ia ON ia.image_id = i.id");
where.push("ia.actress_id = ?");
params.push(opts.actressId);
}
if (opts?.genreId != null) {
joins.push("JOIN image_genres ig ON ig.image_id = i.id");
where.push("ig.genre_id = ?");
params.push(opts.genreId);
}
if (opts?.collectionId != null) {
joins.push("JOIN collection_images ci ON ci.image_id = i.id");
where.push("ci.collection_id = ?");
params.push(opts.collectionId);
if (!opts.sort) order = "ci.position ASC, i.created_at DESC";
}
if (opts?.studioId != null) {
where.push("i.studio_id = ?");
params.push(opts.studioId);
}
if (opts?.labelId != null) {
where.push("i.label_id = ?");
params.push(opts.labelId);
}
if (opts?.seriesId != null) {
where.push("i.series_id = ?");
params.push(opts.seriesId);
}
// Multi-id filters. Skip empties; in AND mode, emit one EXISTS per id; in OR mode, a single IN clause.
const placeholders = (n: number) => Array(n).fill("?").join(",");
function applyMulti(ids: number[] | undefined, mode: FilterMode | undefined, tableExists: string, fkExists: string) {
if (!ids || ids.length === 0) return;
if (mode === "and") {
for (const id of ids) {
where.push(`EXISTS (SELECT 1 FROM ${tableExists} WHERE image_id = i.id AND ${fkExists} = ?)`);
params.push(id);
}
} else {
where.push(`EXISTS (SELECT 1 FROM ${tableExists} WHERE image_id = i.id AND ${fkExists} IN (${placeholders(ids.length)}))`);
params.push(...ids);
}
}
applyMulti(opts?.actressIds, opts?.actressMode, "image_actresses", "actress_id");
applyMulti(opts?.genreIds, opts?.genreMode, "image_genres", "genre_id");
applyMulti(opts?.tagIds, opts?.tagMode, "image_tags", "tag_id");
applyMulti(opts?.collectionIds, opts?.collectionMode, "collection_images", "collection_id");
applyCategoryFilter(where, params, opts?.categoryIds, opts?.categoryMode, placeholders);
// Studios and series live as scalar columns on images; AND across multiple is impossible (a cover has one studio).
if (opts?.studioIds && opts.studioIds.length > 0) {
where.push(`i.studio_id IN (${placeholders(opts.studioIds.length)})`);
params.push(...opts.studioIds);
}
if (opts?.seriesIds && opts.seriesIds.length > 0) {
where.push(`i.series_id IN (${placeholders(opts.seriesIds.length)})`);
params.push(...opts.seriesIds);
}
if (opts?.untagged) {
where.push("NOT EXISTS (SELECT 1 FROM image_tags WHERE image_id = i.id)");
}
if (opts?.uncollected) {
where.push("NOT EXISTS (SELECT 1 FROM collection_images WHERE image_id = i.id)");
}
if (opts?.unwatched) {
where.push("i.watched = 0");
}
if (opts?.watched) {
where.push("i.watched = 1");
}
if (opts?.rated) {
where.push("i.rating IS NOT NULL");
}
if (opts?.unrated) {
where.push("i.rating IS NULL");
}
if (opts?.hasTags) {
where.push("EXISTS (SELECT 1 FROM image_tags WHERE image_id = i.id)");
}
if (opts?.hasCollection) {
where.push("EXISTS (SELECT 1 FROM collection_images WHERE image_id = i.id)");
}
if (opts?.hasVideo) {
where.push("i.has_video = 1");
}
if (opts?.noVideo) {
where.push("i.has_video = 0");
}
if (opts?.marks && opts.marks.length > 0) {
// Multi-select marks are OR'd: a cover that is VIP or Owned matches "vip+owned".
const expr: Record<string, string> = {
vip: "i.is_vip = 1",
favorite: "i.is_favorite = 1",
owned: "i.is_owned = 1",
unmarked: "(i.is_vip = 0 AND i.is_favorite = 0)",
};
const parts = opts.marks.map((m) => expr[m]).filter(Boolean);
if (parts.length > 0) where.push(`(${parts.join(" OR ")})`);
}
if (opts?.letter) {
if (opts.letter === "#") {
where.push("(i.code IS NULL OR substr(upper(i.code), 1, 1) NOT BETWEEN 'A' AND 'Z')");
} else if (/^[A-Z]$/.test(opts.letter)) {
where.push("substr(upper(i.code), 1, 1) = ?");
params.push(opts.letter);
}
}
if (opts?.search) {
const q = opts.search.trim();
if (q) {
const like = `%${q.replace(/[\\%_]/g, (c) => `\\${c}`)}%`;
where.push("(COALESCE(i.code,'') LIKE ? ESCAPE '\\' OR COALESCE(i.title,'') LIKE ? ESCAPE '\\' OR COALESCE(i.notes,'') LIKE ? ESCAPE '\\')");
params.push(like, like, like);
}
}
const offset = Math.max(0, opts?.offset ?? 0);
const sqlText = `
SELECT ${COVER_COLS}
FROM images i
${joins.join("\n ")}
WHERE ${where.join(" AND ")}
ORDER BY ${order}
LIMIT ? OFFSET ?
`;
params.push(limit, offset);
const rows = rawDb.prepare(sqlText).all(...params) as CoverRow[];
return attachActresses(rows.map(rowToCard));
}
/**
* Count rows matching the same filter set as listImages. Used by the
* paginated UI to know totalPages. Mirrors the WHERE-clause logic
* verbatim — keep these in sync if listImages adds a new filter.
*/
export function countImages(opts?: Parameters<typeof listImages>[0]): number {
const joins: string[] = [COVER_BASE_JOIN];
const where: string[] = ["i.deleted_at IS NULL", "i.parent_image_id IS NULL"];
const params: Array<string | number> = [];
if (opts?.tagId != null) {
joins.push("JOIN image_tags it ON it.image_id = i.id");
where.push("it.tag_id = ?");
params.push(opts.tagId);
}
if (opts?.actressId != null) {
joins.push("JOIN image_actresses ia ON ia.image_id = i.id");
where.push("ia.actress_id = ?");
params.push(opts.actressId);
}
if (opts?.genreId != null) {
joins.push("JOIN image_genres ig ON ig.image_id = i.id");
where.push("ig.genre_id = ?");
params.push(opts.genreId);
}
if (opts?.collectionId != null) {
joins.push("JOIN collection_images ci ON ci.image_id = i.id");
where.push("ci.collection_id = ?");
params.push(opts.collectionId);
}
if (opts?.studioId != null) { where.push("i.studio_id = ?"); params.push(opts.studioId); }
if (opts?.labelId != null) { where.push("i.label_id = ?"); params.push(opts.labelId); }
if (opts?.seriesId != null) { where.push("i.series_id = ?"); params.push(opts.seriesId); }
const placeholders = (n: number) => Array(n).fill("?").join(",");
function applyMulti(ids: number[] | undefined, mode: FilterMode | undefined, tableExists: string, fkExists: string) {
if (!ids || ids.length === 0) return;
if (mode === "and") {
for (const id of ids) {
where.push(`EXISTS (SELECT 1 FROM ${tableExists} WHERE image_id = i.id AND ${fkExists} = ?)`);
params.push(id);
}
} else {
where.push(`EXISTS (SELECT 1 FROM ${tableExists} WHERE image_id = i.id AND ${fkExists} IN (${placeholders(ids.length)}))`);
params.push(...ids);
}
}
applyMulti(opts?.actressIds, opts?.actressMode, "image_actresses", "actress_id");
applyMulti(opts?.genreIds, opts?.genreMode, "image_genres", "genre_id");
applyMulti(opts?.tagIds, opts?.tagMode, "image_tags", "tag_id");
applyMulti(opts?.collectionIds, opts?.collectionMode, "collection_images", "collection_id");
applyCategoryFilter(where, params, opts?.categoryIds, opts?.categoryMode, placeholders);
if (opts?.studioIds && opts.studioIds.length > 0) {
where.push(`i.studio_id IN (${placeholders(opts.studioIds.length)})`);
params.push(...opts.studioIds);
}
if (opts?.seriesIds && opts.seriesIds.length > 0) {
where.push(`i.series_id IN (${placeholders(opts.seriesIds.length)})`);
params.push(...opts.seriesIds);
}
if (opts?.untagged) where.push("NOT EXISTS (SELECT 1 FROM image_tags WHERE image_id = i.id)");
if (opts?.uncollected) where.push("NOT EXISTS (SELECT 1 FROM collection_images WHERE image_id = i.id)");
if (opts?.unwatched) where.push("i.watched = 0");
if (opts?.watched) where.push("i.watched = 1");
if (opts?.rated) where.push("i.rating IS NOT NULL");
if (opts?.unrated) where.push("i.rating IS NULL");
if (opts?.hasTags) where.push("EXISTS (SELECT 1 FROM image_tags WHERE image_id = i.id)");
if (opts?.hasCollection) where.push("EXISTS (SELECT 1 FROM collection_images WHERE image_id = i.id)");
if (opts?.hasVideo) where.push("i.has_video = 1");
if (opts?.noVideo) where.push("i.has_video = 0");
if (opts?.marks && opts.marks.length > 0) {
const expr: Record<string, string> = {
vip: "i.is_vip = 1",
favorite: "i.is_favorite = 1",
owned: "i.is_owned = 1",
unmarked: "(i.is_vip = 0 AND i.is_favorite = 0)",
};
const parts = opts.marks.map((m) => expr[m]).filter(Boolean);
if (parts.length > 0) where.push(`(${parts.join(" OR ")})`);
}
if (opts?.letter) {
if (opts.letter === "#") {
where.push("(i.code IS NULL OR substr(upper(i.code), 1, 1) NOT BETWEEN 'A' AND 'Z')");
} else if (/^[A-Z]$/.test(opts.letter)) {
where.push("substr(upper(i.code), 1, 1) = ?");
params.push(opts.letter);
}
}
if (opts?.search) {
const q = opts.search.trim();
if (q) {
const like = `%${q.replace(/[\\%_]/g, (c) => `\\${c}`)}%`;
where.push("(COALESCE(i.code,'') LIKE ? ESCAPE '\\' OR COALESCE(i.title,'') LIKE ? ESCAPE '\\' OR COALESCE(i.notes,'') LIKE ? ESCAPE '\\')");
params.push(like, like, like);
}
}
const sqlText = `
SELECT COUNT(*) AS n
FROM images i
${joins.join("\n ")}
WHERE ${where.join(" AND ")}
`;
const row = rawDb.prepare(sqlText).get(...params) as { n: number };
return row.n;
}
export function getImageDetail(id: number) {
const image = db
.select()
.from(images)
.where(and(eq(images.id, id), isNull(images.deletedAt), isNull(images.parentImageId)))
.get();
if (!image) return null;
const studio = image.studioId
? rawDb.prepare(`SELECT id, name, slug FROM studios WHERE id = ?`).get(image.studioId) as { id: number; name: string; slug: string } | undefined
: null;
const label = image.labelId
? rawDb.prepare(`SELECT id, name, slug FROM labels WHERE id = ?`).get(image.labelId) as { id: number; name: string; slug: string } | undefined
: null;
const seriesRow = image.seriesId
? rawDb.prepare(`SELECT id, name, slug FROM series WHERE id = ?`).get(image.seriesId) as { id: number; name: string; slug: string } | undefined
: null;
const actresses = rawDb.prepare(`
SELECT a.id, a.name, a.slug FROM actresses a
JOIN image_actresses ia ON ia.actress_id = a.id
WHERE ia.image_id = ? ORDER BY a.name
`).all(id) as Array<{ id: number; name: string; slug: string }>;
const genres = rawDb.prepare(`
SELECT g.id, g.name, g.slug FROM genres g
JOIN image_genres ig ON ig.genre_id = g.id
WHERE ig.image_id = ? ORDER BY g.name
`).all(id) as Array<{ id: number; name: string; slug: string }>;
const tagsList = rawDb.prepare(`
SELECT t.id, t.name, t.color
FROM tags t JOIN image_tags it ON it.tag_id = t.id
WHERE it.image_id = ? ORDER BY t.name
`).all(id) as Array<{ id: number; name: string; color: string | null }>;
const collectionsList = rawDb.prepare(`
SELECT c.id, c.name, c.slug FROM collections c
JOIN collection_images ci ON ci.collection_id = c.id
WHERE ci.image_id = ?
`).all(id) as Array<{ id: number; name: string; slug: string }>;
return { image, studio: studio ?? null, label: label ?? null, series: seriesRow ?? null, actresses, genres, tags: tagsList, collections: collectionsList };
}
export function searchCovers(query: string, limit = 200): CardImage[] {
const trimmed = query.trim();
if (!trimmed) return [];
const tokens = trimmed.split(/\s+/).filter(Boolean);
// 1. FTS over code/title/director/notes — preserves rank ordering.
const ftsQuery = tokens.map((t) => `"${t.replace(/"/g, '""')}"*`).join(" ");
const ftsRows = rawDb.prepare(`
SELECT i.id
FROM covers_fts f
JOIN images i ON i.id = f.rowid
WHERE covers_fts MATCH ? AND i.deleted_at IS NULL AND i.parent_image_id IS NULL
ORDER BY rank
`).all(ftsQuery) as Array<{ id: number }>;
// 2. Actress name match — all tokens must appear in name OR alt_names, in any
// order. This makes "Ichika Matsumoto" == "Matsumoto Ichika".
const conds = tokens
.map(() => `(LOWER(a.name) LIKE ? ESCAPE '\\' OR LOWER(COALESCE(a.alt_names, '')) LIKE ? ESCAPE '\\')`)
.join(" AND ");
const params: string[] = [];
for (const t of tokens) {
const p = `%${escapeLike(t.toLowerCase())}%`;
params.push(p, p);
}
const actressRows = rawDb.prepare(`
SELECT DISTINCT i.id
FROM images i
JOIN image_actresses ia ON ia.image_id = i.id
JOIN actresses a ON a.id = ia.actress_id
WHERE ${conds} AND i.deleted_at IS NULL AND i.parent_image_id IS NULL
`).all(...params) as Array<{ id: number }>;
// Merge: FTS first (better ranked), then actress matches.
const seen = new Set<number>();
const orderedIds: number[] = [];
for (const r of ftsRows) if (!seen.has(r.id)) { seen.add(r.id); orderedIds.push(r.id); }
for (const r of actressRows) if (!seen.has(r.id)) { seen.add(r.id); orderedIds.push(r.id); }
if (orderedIds.length === 0) return [];
const limited = orderedIds.slice(0, limit);
const placeholders = limited.map(() => "?").join(",");
const rows = rawDb.prepare(`
SELECT ${COVER_COLS}
FROM images i
${COVER_BASE_JOIN}
WHERE i.id IN (${placeholders})
`).all(...limited) as CoverRow[];
const byId = new Map(rows.map((r) => [r.id, r]));
return attachActresses(limited.map((id) => byId.get(id)).filter((r): r is CoverRow => !!r).map(rowToCard));
}
export function listImagesByIds(ids: number[]): CardImage[] {
if (!ids || ids.length === 0) return [];
const placeholders = ids.map(() => "?").join(",");
const rows = rawDb.prepare(`
SELECT ${COVER_COLS}
FROM images i
${COVER_BASE_JOIN}
WHERE i.id IN (${placeholders}) AND i.deleted_at IS NULL
`).all(...ids) as CoverRow[];
const byId = new Map(rows.map((r) => [r.id, r]));
// Preserve caller's order (queue order matters).
return attachActresses(
ids.map((id) => byId.get(id)).filter((r): r is CoverRow => !!r).map(rowToCard),
);
}
export type TagSort = "az" | "count";
export interface TagRow {
id: number;
name: string;
color: string | null;
count: number;
categoryId: number | null;
categoryName: string | null;
categoryColor: string | null;
}
export function listAllTags(sort: TagSort = "az"): TagRow[] {
const order = sort === "count"
? "count DESC, t.name COLLATE NOCASE"
: "t.name COLLATE NOCASE";
return rawDb.prepare(`
SELECT
t.id, t.name, t.color,
COUNT(CASE WHEN i.id IS NOT NULL AND i.deleted_at IS NULL THEN 1 END) AS count,
t.category_id AS categoryId,
tc.name AS categoryName,
tc.color AS categoryColor
FROM tags t
LEFT JOIN image_tags it ON it.tag_id = t.id
LEFT JOIN images i ON i.id = it.image_id
LEFT JOIN tag_categories tc ON tc.id = t.category_id
GROUP BY t.id ORDER BY ${order}
`).all() as TagRow[];
}
export interface TagCategoryRow {
id: number;
name: string;
slug: string;
color: string | null;
description: string | null;
tagCount: number;
imageCount: number;
coverPortraitPath: string | null;
coverPortraitZoom: number;
coverPortraitOffsetX: number;
coverPortraitOffsetY: number;
coverLandscapePath: string | null;
coverLandscapeZoom: number;
coverLandscapeOffsetX: number;
coverLandscapeOffsetY: number;
}
export type CategorySort = "az" | "count";
export function listAllTagCategories(sort: CategorySort = "az"): TagCategoryRow[] {
const order = sort === "count"
? "imageCount DESC, c.name COLLATE NOCASE"
: "c.name COLLATE NOCASE";
return rawDb.prepare(`
SELECT
c.id, c.name, c.slug, c.color, c.description,
c.cover_portrait_path AS coverPortraitPath,
c.cover_portrait_zoom AS coverPortraitZoom,
c.cover_portrait_offset_x AS coverPortraitOffsetX,
c.cover_portrait_offset_y AS coverPortraitOffsetY,
c.cover_landscape_path AS coverLandscapePath,
c.cover_landscape_zoom AS coverLandscapeZoom,
c.cover_landscape_offset_x AS coverLandscapeOffsetX,
c.cover_landscape_offset_y AS coverLandscapeOffsetY,
COUNT(DISTINCT t.id) AS tagCount,
COUNT(DISTINCT CASE WHEN i.id IS NOT NULL AND i.deleted_at IS NULL THEN i.id END) AS imageCount
FROM tag_categories c
LEFT JOIN tags t ON t.category_id = c.id
LEFT JOIN image_tags it ON it.tag_id = t.id
LEFT JOIN images i ON i.id = it.image_id
GROUP BY c.id ORDER BY ${order}
`).all() as TagCategoryRow[];
}
export interface TagCategoryDetail {
id: number;
name: string;
slug: string;
color: string | null;
description: string | null;
coverPortraitPath: string | null;
coverPortraitZoom: number;
coverPortraitOffsetX: number;
coverPortraitOffsetY: number;
coverLandscapePath: string | null;
coverLandscapeZoom: number;
coverLandscapeOffsetX: number;
coverLandscapeOffsetY: number;
}
export function getTagCategoryBySlug(slug: string): TagCategoryDetail | undefined {
return rawDb.prepare(`
SELECT
id, name, slug, color, description,
cover_portrait_path AS coverPortraitPath,
cover_portrait_zoom AS coverPortraitZoom,
cover_portrait_offset_x AS coverPortraitOffsetX,
cover_portrait_offset_y AS coverPortraitOffsetY,
cover_landscape_path AS coverLandscapePath,
cover_landscape_zoom AS coverLandscapeZoom,
cover_landscape_offset_x AS coverLandscapeOffsetX,
cover_landscape_offset_y AS coverLandscapeOffsetY
FROM tag_categories WHERE slug = ?
`).get(slug) as TagCategoryDetail | undefined;
}
export function listTagsInCategory(categoryId: number): Array<{ id: number; name: string; color: string | null; count: number }> {
return rawDb.prepare(`
SELECT t.id, t.name, t.color, COUNT(CASE WHEN i.id IS NOT NULL AND i.deleted_at IS NULL THEN 1 END) AS count
FROM tags t
LEFT JOIN image_tags it ON it.tag_id = t.id
LEFT JOIN images i ON i.id = it.image_id
WHERE t.category_id = ?
GROUP BY t.id ORDER BY t.name COLLATE NOCASE
`).all(categoryId) as Array<{ id: number; name: string; color: string | null; count: number }>;
}
export function listAllCollections() {
return rawDb.prepare(`
SELECT c.id, c.name, c.slug, c.description,
c.cover_portrait_path AS coverPortraitPath,
c.cover_portrait_zoom AS coverPortraitZoom,
c.cover_portrait_offset_x AS coverPortraitOffsetX,
c.cover_portrait_offset_y AS coverPortraitOffsetY,
c.cover_landscape_path AS coverLandscapePath,
c.cover_landscape_zoom AS coverLandscapeZoom,
c.cover_landscape_offset_x AS coverLandscapeOffsetX,
c.cover_landscape_offset_y AS coverLandscapeOffsetY,
(SELECT thumb_path FROM images WHERE id = c.cover_image_id AND deleted_at IS NULL) AS cover_thumb,
(SELECT thumb_path FROM images i JOIN collection_images ci ON ci.image_id = i.id WHERE ci.collection_id = c.id AND i.deleted_at IS NULL ORDER BY ci.position LIMIT 1) AS first_thumb,
(SELECT COUNT(*) FROM collection_images ci JOIN images i ON i.id = ci.image_id WHERE ci.collection_id = c.id AND i.deleted_at IS NULL) AS count
FROM collections c
ORDER BY c.position ASC, c.id ASC
`).all() as Array<{
id: number; name: string; slug: string; description: string | null;
coverPortraitPath: string | null; coverPortraitZoom: number; coverPortraitOffsetX: number; coverPortraitOffsetY: number;
coverLandscapePath: string | null; coverLandscapeZoom: number; coverLandscapeOffsetX: number; coverLandscapeOffsetY: number;
cover_thumb: string | null; first_thumb: string | null; count: number;
}>;
}
export function getCollection(id: number) {
const c = db.select().from(collections).where(eq(collections.id, id)).get();
if (!c) return null;
return c;
}
export function getCollectionBySlug(slug: string) {
return rawDb.prepare(`SELECT * FROM collections WHERE slug = ?`).get(slug) as
| { id: number; name: string; slug: string; description: string | null; cover_image_id: number | null; created_at: number }
| undefined ?? null;
}
export interface ActressPortrait {
portraitPath: string | null;
portraitZoom: number;
portraitOffsetX: number;
portraitOffsetY: number;
}
export interface ActressPortraitSlot {
path: string | null;
zoom: number;
offsetX: number;
offsetY: number;
}
export type PortraitSlotKey = "1" | "2" | "3" | "4" | "h";
export interface ActressAllPortraits {
p1: ActressPortraitSlot;
p2: ActressPortraitSlot;
p3: ActressPortraitSlot;
p4: ActressPortraitSlot;
ph: ActressPortraitSlot;
}
export interface ActressCategory {
id: number;
name: string;
slug: string;
color: string | null;
icon: string | null;
priority: number;
builtin: number;
}
export type ActressListItem = {
id: number;
name: string;
slug: string;
count: number;
altNames: string | null;
categories: ActressCategory[];
portraits: ActressAllPortraits;
} & ActressPortrait;
export function listActressCategories(): ActressCategory[] {
return rawDb.prepare(`
SELECT id, name, slug, color, icon, priority, builtin
FROM actress_categories
ORDER BY priority DESC, name
`).all() as ActressCategory[];
}
export type CoStar = {
id: number;
name: string;
slug: string;
shared: number;
portraitPath: string | null;
portraitZoom: number;
portraitOffsetX: number;
portraitOffsetY: number;
};
export function listCoStars(actressId: number, limit = 24): CoStar[] {
return rawDb.prepare(`
SELECT a.id, a.name, a.slug,
a.portrait_path AS portraitPath,
a.portrait_zoom AS portraitZoom,
a.portrait_offset_x AS portraitOffsetX,
a.portrait_offset_y AS portraitOffsetY,
COUNT(DISTINCT ia2.image_id) AS shared
FROM image_actresses ia
JOIN image_actresses ia2
ON ia2.image_id = ia.image_id AND ia2.actress_id != ia.actress_id
JOIN actresses a ON a.id = ia2.actress_id
JOIN images i ON i.id = ia.image_id
WHERE ia.actress_id = ?
AND i.deleted_at IS NULL
AND i.parent_image_id IS NULL
GROUP BY a.id
ORDER BY shared DESC, a.name ASC
LIMIT ?
`).all(actressId, limit) as CoStar[];
}
export function listAllActresses(): ActressListItem[] {
const raw = rawDb.prepare(`
SELECT a.id, a.name, a.slug,
a.alt_names AS altNames,
a.portrait_path AS portraitPath,
a.portrait_zoom AS portraitZoom,
a.portrait_offset_x AS portraitOffsetX,
a.portrait_offset_y AS portraitOffsetY,
a.portrait2_path AS p2Path, a.portrait2_zoom AS p2Zoom, a.portrait2_offset_x AS p2Ox, a.portrait2_offset_y AS p2Oy,
a.portrait3_path AS p3Path, a.portrait3_zoom AS p3Zoom, a.portrait3_offset_x AS p3Ox, a.portrait3_offset_y AS p3Oy,
a.portrait4_path AS p4Path, a.portrait4_zoom AS p4Zoom, a.portrait4_offset_x AS p4Ox, a.portrait4_offset_y AS p4Oy,
a.portraith_path AS phPath, a.portraith_zoom AS phZoom, a.portraith_offset_x AS phOx, a.portraith_offset_y AS phOy,
COUNT(CASE WHEN i.id IS NOT NULL AND i.deleted_at IS NULL AND i.parent_image_id IS NULL THEN 1 END) AS count
FROM actresses a
LEFT JOIN image_actresses ia ON ia.actress_id = a.id
LEFT JOIN images i ON i.id = ia.image_id
GROUP BY a.id ORDER BY count DESC, a.name
`).all() as Array<{
id: number; name: string; slug: string; count: number; altNames: string | null;
portraitPath: string | null; portraitZoom: number; portraitOffsetX: number; portraitOffsetY: number;
p2Path: string | null; p2Zoom: number; p2Ox: number; p2Oy: number;
p3Path: string | null; p3Zoom: number; p3Ox: number; p3Oy: number;
p4Path: string | null; p4Zoom: number; p4Ox: number; p4Oy: number;
phPath: string | null; phZoom: number; phOx: number; phOy: number;
}>;
const rows: ActressListItem[] = raw.map((r) => ({
id: r.id, name: r.name, slug: r.slug, count: r.count, altNames: r.altNames,
portraitPath: r.portraitPath, portraitZoom: r.portraitZoom, portraitOffsetX: r.portraitOffsetX, portraitOffsetY: r.portraitOffsetY,
categories: [],
portraits: {
p1: { path: r.portraitPath, zoom: r.portraitZoom, offsetX: r.portraitOffsetX, offsetY: r.portraitOffsetY },
p2: { path: r.p2Path, zoom: r.p2Zoom, offsetX: r.p2Ox, offsetY: r.p2Oy },
p3: { path: r.p3Path, zoom: r.p3Zoom, offsetX: r.p3Ox, offsetY: r.p3Oy },
p4: { path: r.p4Path, zoom: r.p4Zoom, offsetX: r.p4Ox, offsetY: r.p4Oy },
ph: { path: r.phPath, zoom: r.phZoom, offsetX: r.phOx, offsetY: r.phOy },
},
}));
if (rows.length === 0) return rows;
const ids = rows.map((r) => r.id);
const placeholders = ids.map(() => "?").join(",");
const catRows = rawDb.prepare(`
SELECT m.actress_id AS actressId,
c.id, c.name, c.slug, c.color, c.icon, c.priority, c.builtin
FROM actress_categories_map m
JOIN actress_categories c ON c.id = m.category_id
WHERE m.actress_id IN (${placeholders})
ORDER BY c.priority DESC, c.name
`).all(...ids) as Array<{ actressId: number } & ActressCategory>;
const byActress = new Map<number, ActressCategory[]>();
for (const row of catRows) {
const { actressId, ...cat } = row;
if (!byActress.has(actressId)) byActress.set(actressId, []);
byActress.get(actressId)!.push(cat);
}
return rows.map((r) => ({ ...r, categories: byActress.get(r.id) ?? [] }));
}
export function getActressCategoryIds(actressId: number): number[] {
const rows = rawDb.prepare(`SELECT category_id AS id FROM actress_categories_map WHERE actress_id = ?`).all(actressId) as Array<{ id: number }>;
return rows.map((r) => r.id);
}
export function getActressBySlug(slug: string) {
const row = rawDb.prepare(`
SELECT id, name, slug, alt_names AS altNames, notes,
portrait_path AS portraitPath,
portrait_zoom AS portraitZoom,
portrait_offset_x AS portraitOffsetX,
portrait_offset_y AS portraitOffsetY,
portrait2_path AS p2Path, portrait2_zoom AS p2Zoom, portrait2_offset_x AS p2Ox, portrait2_offset_y AS p2Oy,
portrait3_path AS p3Path, portrait3_zoom AS p3Zoom, portrait3_offset_x AS p3Ox, portrait3_offset_y AS p3Oy,
portrait4_path AS p4Path, portrait4_zoom AS p4Zoom, portrait4_offset_x AS p4Ox, portrait4_offset_y AS p4Oy,
portraith_path AS phPath, portraith_zoom AS phZoom, portraith_offset_x AS phOx, portraith_offset_y AS phOy,
born_on AS bornOn, height_cm AS heightCm, weight_kg AS weightKg, cup_size AS cupSize
FROM actresses WHERE slug = ?
`).get(slug) as
| ({
id: number; name: string; slug: string; altNames: string | null; notes: string | null;
p2Path: string | null; p2Zoom: number; p2Ox: number; p2Oy: number;
p3Path: string | null; p3Zoom: number; p3Ox: number; p3Oy: number;
p4Path: string | null; p4Zoom: number; p4Ox: number; p4Oy: number;
phPath: string | null; phZoom: number; phOx: number; phOy: number;
bornOn: string | null; heightCm: number | null; weightKg: number | null; cupSize: string | null;
} & ActressPortrait)
| undefined;
if (!row) return null;
const portraits: ActressAllPortraits = {
p1: { path: row.portraitPath, zoom: row.portraitZoom, offsetX: row.portraitOffsetX, offsetY: row.portraitOffsetY },
p2: { path: row.p2Path, zoom: row.p2Zoom, offsetX: row.p2Ox, offsetY: row.p2Oy },
p3: { path: row.p3Path, zoom: row.p3Zoom, offsetX: row.p3Ox, offsetY: row.p3Oy },
p4: { path: row.p4Path, zoom: row.p4Zoom, offsetX: row.p4Ox, offsetY: row.p4Oy },
ph: { path: row.phPath, zoom: row.phZoom, offsetX: row.phOx, offsetY: row.phOy },
};
const categories = rawDb.prepare(`
SELECT c.id, c.name, c.slug, c.color, c.icon, c.priority, c.builtin
FROM actress_categories_map m
JOIN actress_categories c ON c.id = m.category_id
WHERE m.actress_id = ?
ORDER BY c.priority DESC, c.name
`).all(row.id) as ActressCategory[];
return { ...row, categories, portraits };
}
export function listAllStudios() {
return rawDb.prepare(`
SELECT s.id, s.name, s.slug,
COUNT(CASE WHEN i.id IS NOT NULL AND i.deleted_at IS NULL AND i.parent_image_id IS NULL THEN 1 END) AS count
FROM studios s
LEFT JOIN images i ON i.studio_id = s.id
GROUP BY s.id ORDER BY count DESC, s.name
`).all() as Array<{ id: number; name: string; slug: string; count: number }>;
}
export function getStudioBySlug(slug: string) {
return rawDb.prepare(`SELECT id, name, slug, notes FROM studios WHERE slug = ?`).get(slug) as
| { id: number; name: string; slug: string; notes: string | null }
| undefined ?? null;
}
export function listAllLabels() {
return rawDb.prepare(`
SELECT l.id, l.name, l.slug,
COUNT(CASE WHEN i.id IS NOT NULL AND i.deleted_at IS NULL AND i.parent_image_id IS NULL THEN 1 END) AS count
FROM labels l
LEFT JOIN images i ON i.label_id = l.id
GROUP BY l.id ORDER BY count DESC, l.name
`).all() as Array<{ id: number; name: string; slug: string; count: number }>;
}
export function getLabelBySlug(slug: string) {
return rawDb.prepare(`SELECT id, name, slug, notes FROM labels WHERE slug = ?`).get(slug) as
| { id: number; name: string; slug: string; notes: string | null }
| undefined ?? null;
}
export function listAllSeries() {
return rawDb.prepare(`
SELECT s.id, s.name, s.slug,
COUNT(CASE WHEN i.id IS NOT NULL AND i.deleted_at IS NULL AND i.parent_image_id IS NULL THEN 1 END) AS count
FROM series s
LEFT JOIN images i ON i.series_id = s.id
GROUP BY s.id ORDER BY count DESC, s.name
`).all() as Array<{ id: number; name: string; slug: string; count: number }>;
}
export function getSeriesBySlug(slug: string) {
return rawDb.prepare(`SELECT id, name, slug, notes FROM series WHERE slug = ?`).get(slug) as
| { id: number; name: string; slug: string; notes: string | null }
| undefined ?? null;
}
export function listAllGenres() {
return rawDb.prepare(`
SELECT g.id, g.name, g.slug,
COUNT(CASE WHEN i.id IS NOT NULL AND i.deleted_at IS NULL AND i.parent_image_id IS NULL THEN 1 END) AS count
FROM genres g
LEFT JOIN image_genres ig ON ig.genre_id = g.id
LEFT JOIN images i ON i.id = ig.image_id
GROUP BY g.id ORDER BY count DESC, g.name
`).all() as Array<{ id: number; name: string; slug: string; count: number }>;
}
export type NeighborImage = { id: number; code: string | null };
export function getNeighborImageIds(currentId: number): { prev: NeighborImage | null; next: NeighborImage | null } {
const cur = rawDb.prepare(`SELECT created_at FROM images WHERE id = ?`).get(currentId) as { created_at: number } | undefined;
if (!cur) return { prev: null, next: null };
const next = rawDb.prepare(`
SELECT id, code FROM images
WHERE (created_at, id) > (?, ?) AND deleted_at IS NULL AND parent_image_id IS NULL
ORDER BY created_at ASC, id ASC
LIMIT 1
`).get(cur.created_at, currentId) as NeighborImage | undefined;
const prev = rawDb.prepare(`
SELECT id, code FROM images
WHERE (created_at, id) < (?, ?) AND deleted_at IS NULL AND parent_image_id IS NULL
ORDER BY created_at DESC, id DESC
LIMIT 1
`).get(cur.created_at, currentId) as NeighborImage | undefined;
return { prev: prev ?? null, next: next ?? null };
}
export function getRandomImage(excludeId?: number): NeighborImage | null {
const row = rawDb.prepare(`
SELECT id, code FROM images
WHERE id != COALESCE(?, -1) AND deleted_at IS NULL AND parent_image_id IS NULL
ORDER BY RANDOM()
LIMIT 1
`).get(excludeId ?? null) as NeighborImage | undefined;
return row ?? null;
}
export function getRandomImageId(excludeId?: number): number | null {
return getRandomImage(excludeId)?.id ?? null;
}
export function listAttachedImages(parentId: number): Array<{ id: number; thumbPath: string; width: number; height: number; filename: string; sha256: string }> {
return rawDb.prepare(`
SELECT id, thumb_path AS thumbPath, width, height, filename, sha256
FROM images
WHERE parent_image_id = ? AND deleted_at IS NULL
ORDER BY created_at ASC, id ASC
`).all(parentId) as Array<{ id: number; thumbPath: string; width: number; height: number; filename: string; sha256: string }>;
}
export function getImageIdByCode(code: string): number | null {
const row = rawDb.prepare(`
SELECT id FROM images
WHERE code = ? AND deleted_at IS NULL AND parent_image_id IS NULL
ORDER BY id ASC
LIMIT 1
`).get(code) as { id: number } | undefined;
return row?.id ?? null;
}
export interface ContextCoverInfo {
id: number;
code: string | null;
title: string | null;
isVip: boolean;
isFavorite: boolean;
isOwned: boolean;
isWatched: boolean;
rating: number | null;
actresses: string[];
}
export interface ContextTagOption {
id: number;
name: string;
color: string | null;
/** How many of the supplied imageIds already have this tag. */
count: number;
}
export interface ContextCollectionOption {
id: number;
name: string;
count: number;
}
export interface ContextData {
covers: ContextCoverInfo[];
tags: ContextTagOption[];
collections: ContextCollectionOption[];
recentTags: Array<{ id: number; name: string; color: string | null }>;
recentCollections: Array<{ id: number; name: string }>;
selectedCount: number;
}
/**
* Bundle every piece of context the right-click menu needs in a single
* round-trip. Per-tag/per-collection `count` lets the UI render
* tri-state (all / partial / none) for bulk operations.
*/
export function getImageContextData(imageIds: number[]): ContextData {
const allTags = listAllTags();
const allCollections = listAllCollections();
const recentTags = rawDb.prepare(`
SELECT id, name, color FROM tags WHERE last_used_at > 0 ORDER BY last_used_at DESC LIMIT 6
`).all() as Array<{ id: number; name: string; color: string | null }>;
const recentCollections = rawDb.prepare(`
SELECT id, name FROM collections WHERE last_used_at > 0 ORDER BY last_used_at DESC LIMIT 6
`).all() as Array<{ id: number; name: string }>;
if (imageIds.length === 0) {
return {
covers: [],
tags: allTags.map(({ id, name, color }) => ({ id, name, color, count: 0 })),
collections: allCollections.map((c) => ({ id: c.id, name: c.name, count: 0 })),
recentTags,
recentCollections,
selectedCount: 0,
};
}
const placeholders = imageIds.map(() => "?").join(",");
// Per-cover info for the header (single) or batch summary (bulk).
const coverRows = rawDb.prepare(`
SELECT id, code, title, is_vip AS isVip, is_favorite AS isFavorite,
is_owned AS isOwned, watched AS isWatched, rating
FROM images WHERE id IN (${placeholders})
`).all(...imageIds) as Array<{
id: number; code: string | null; title: string | null;
isVip: number; isFavorite: number; isOwned: number; isWatched: number;
rating: number | null;
}>;
// Actresses per cover, grouped.
const actressRows = rawDb.prepare(`
SELECT ia.image_id, a.name FROM image_actresses ia
JOIN actresses a ON a.id = ia.actress_id
WHERE ia.image_id IN (${placeholders})
ORDER BY a.name
`).all(...imageIds) as Array<{ image_id: number; name: string }>;
const actressesByImage = new Map<number, string[]>();
for (const r of actressRows) {
const arr = actressesByImage.get(r.image_id) ?? [];
arr.push(r.name);
actressesByImage.set(r.image_id, arr);
}
const covers: ContextCoverInfo[] = coverRows.map((r) => ({
id: r.id,
code: r.code,
title: r.title,
isVip: !!r.isVip,
isFavorite: !!r.isFavorite,
isOwned: !!r.isOwned,
isWatched: !!r.isWatched,
rating: r.rating,
actresses: actressesByImage.get(r.id) ?? [],
}));
// Per-tag and per-collection coverage counts.
const tagCountRows = rawDb.prepare(`
SELECT tag_id, COUNT(DISTINCT image_id) AS n FROM image_tags
WHERE image_id IN (${placeholders})
GROUP BY tag_id
`).all(...imageIds) as Array<{ tag_id: number; n: number }>;
const tagCountById = new Map(tagCountRows.map((r) => [r.tag_id, r.n] as const));
const collCountRows = rawDb.prepare(`
SELECT collection_id, COUNT(DISTINCT image_id) AS n FROM collection_images
WHERE image_id IN (${placeholders})
GROUP BY collection_id
`).all(...imageIds) as Array<{ collection_id: number; n: number }>;
const collCountById = new Map(collCountRows.map((r) => [r.collection_id, r.n] as const));
return {
covers,
tags: allTags.map(({ id, name, color }) => ({ id, name, color, count: tagCountById.get(id) ?? 0 })),
collections: allCollections.map((c) => ({ id: c.id, name: c.name, count: collCountById.get(c.id) ?? 0 })),
recentTags,
recentCollections,
selectedCount: imageIds.length,
};
}
export interface LibraryStats {
// Cover counts
images: number;
attached: number;
trashed: number;
// Entity counts
actresses: number;
studios: number;
series: number;
labels: number;
genres: number;
// Tagging
tags: number;
tagCategories: number;
collections: number;
// State
watched: number;
vip: number;
favorite: number;
owned: number;
rated: number;
// Disk
totalBytes: number;
earliestImportedAt: number | null;
latestImportedAt: number | null;
}
export function libraryStats(): LibraryStats {
return rawDb.prepare(`
SELECT
(SELECT COUNT(*) FROM images WHERE deleted_at IS NULL AND parent_image_id IS NULL) AS images,
(SELECT COUNT(*) FROM images WHERE deleted_at IS NULL AND parent_image_id IS NOT NULL) AS attached,
(SELECT COUNT(*) FROM images WHERE deleted_at IS NOT NULL) AS trashed,
(SELECT COUNT(*) FROM actresses) AS actresses,
(SELECT COUNT(*) FROM studios) AS studios,
(SELECT COUNT(*) FROM series) AS series,
(SELECT COUNT(*) FROM labels) AS labels,
(SELECT COUNT(*) FROM genres) AS genres,
(SELECT COUNT(*) FROM tags) AS tags,
(SELECT COUNT(*) FROM tag_categories) AS tagCategories,
(SELECT COUNT(*) FROM collections) AS collections,
(SELECT COUNT(*) FROM images WHERE deleted_at IS NULL AND parent_image_id IS NULL AND watched = 1) AS watched,
(SELECT COUNT(*) FROM images WHERE deleted_at IS NULL AND parent_image_id IS NULL AND is_vip = 1) AS vip,
(SELECT COUNT(*) FROM images WHERE deleted_at IS NULL AND parent_image_id IS NULL AND is_favorite = 1) AS favorite,
(SELECT COUNT(*) FROM images WHERE deleted_at IS NULL AND parent_image_id IS NULL AND is_owned = 1) AS owned,
(SELECT COUNT(*) FROM images WHERE deleted_at IS NULL AND parent_image_id IS NULL AND rating IS NOT NULL) AS rated,
(SELECT COALESCE(SUM(bytes), 0) FROM images WHERE deleted_at IS NULL) AS totalBytes,
(SELECT MIN(imported_at) FROM images WHERE deleted_at IS NULL) AS earliestImportedAt,
(SELECT MAX(imported_at) FROM images WHERE deleted_at IS NULL) AS latestImportedAt
`).get() as LibraryStats;
}
export function listTrashedImages(): Array<CardImage & { deletedAt: number }> {
const rows = rawDb.prepare(`
SELECT ${COVER_COLS}, i.deleted_at
FROM images i
${COVER_BASE_JOIN}
WHERE i.deleted_at IS NOT NULL AND i.parent_image_id IS NULL
ORDER BY i.deleted_at DESC
`).all() as Array<CoverRow & { deleted_at: number }>;
const cards = attachActresses(rows.map(rowToCard));
return cards.map((c, i) => ({ ...c, deletedAt: rows[i].deleted_at }));
}
export function getTrashedImagesByIds(ids: number[]) {
if (ids.length === 0) return [];
return rawDb.prepare(`
SELECT id, rel_path, thumb_path, deleted_at
FROM images
WHERE deleted_at IS NOT NULL AND id IN (${ids.map(() => "?").join(",")})
`).all(...ids) as Array<{ id: number; rel_path: string; thumb_path: string; deleted_at: number }>;
}
export { db, eq, inArray, and, sql, desc };
function escapeLike(s: string): string {
return s.replace(/[\\%_]/g, (c) => `\\${c}`);
}
Reference in New Issue View Git Blame Copy Permalink