games_app/backend/dist-test-faq/repositories/faqRepository.js

244 lines
10 KiB
JavaScript

"use strict";
Object.defineProperty(exports, "__esModule", { value: true });
exports.listCategories = listCategories;
exports.getCategoryById = getCategoryById;
exports.getCategoryBySlug = getCategoryBySlug;
exports.createCategory = createCategory;
exports.updateCategory = updateCategory;
exports.setCategoryDocument = setCategoryDocument;
exports.clearCategoryDocument = clearCategoryDocument;
exports.deleteCategory = deleteCategory;
exports.listPublicEntries = listPublicEntries;
exports.listPopularEntries = listPopularEntries;
exports.getEntryById = getEntryById;
exports.incrementViewCount = incrementViewCount;
exports.recordHelpfulVote = recordHelpfulVote;
exports.listAdminEntries = listAdminEntries;
exports.createEntry = createEntry;
exports.updateEntry = updateEntry;
exports.deleteEntry = deleteEntry;
exports.countEntriesInCategory = countEntriesInCategory;
const connection_1 = require("../db/connection");
async function listCategories(activeOnly = false) {
if (activeOnly) {
return (0, connection_1.query)(`SELECT * FROM faq_categories WHERE is_active = TRUE ORDER BY sort_order ASC, name ASC`);
}
return (0, connection_1.query)(`SELECT * FROM faq_categories ORDER BY sort_order ASC, name ASC`);
}
async function getCategoryById(id) {
const rows = await (0, connection_1.query)(`SELECT * FROM faq_categories WHERE id = $1`, [id]);
return rows[0] ?? null;
}
async function getCategoryBySlug(slug) {
const rows = await (0, connection_1.query)(`SELECT * FROM faq_categories WHERE slug = $1`, [slug]);
return rows[0] ?? null;
}
async function createCategory(input) {
const rows = await (0, connection_1.query)(`INSERT INTO faq_categories (slug, name, description, sort_order, is_active)
VALUES ($1, $2, $3, $4, $5) RETURNING *`, [
input.slug,
input.name,
input.description ?? null,
input.sortOrder ?? 0,
input.isActive ?? true,
]);
return rows[0];
}
async function updateCategory(id, patch) {
const c = await getCategoryById(id);
if (!c)
return null;
const rows = await (0, connection_1.query)(`UPDATE faq_categories SET
slug = $2,
name = $3,
description = $4,
sort_order = $5,
is_active = $6,
updated_at = NOW()
WHERE id = $1 RETURNING *`, [
id,
patch.slug ?? c.slug,
patch.name ?? c.name,
patch.description !== undefined ? patch.description : c.description,
patch.sortOrder ?? c.sort_order,
patch.isActive ?? c.is_active,
]);
return rows[0] ?? null;
}
/** Atomically set one language PDF pointer (safe replace). */
async function setCategoryDocument(id, lang, filename, originalName) {
const filenameCol = lang === 'fr' ? 'pdf_fr_filename' : 'pdf_en_filename';
const originalCol = lang === 'fr' ? 'pdf_fr_original_name' : 'pdf_en_original_name';
const rows = await (0, connection_1.query)(`UPDATE faq_categories SET
${filenameCol} = $2,
${originalCol} = $3,
updated_at = NOW()
WHERE id = $1 RETURNING *`, [id, filename, originalName]);
return rows[0] ?? null;
}
/** Clear one language PDF pointer; returns previous filenames for disk cleanup. */
async function clearCategoryDocument(id, lang) {
const c = await getCategoryById(id);
if (!c)
return null;
const removedFilename = lang === 'fr' ? c.pdf_fr_filename : c.pdf_en_filename;
const filenameCol = lang === 'fr' ? 'pdf_fr_filename' : 'pdf_en_filename';
const originalCol = lang === 'fr' ? 'pdf_fr_original_name' : 'pdf_en_original_name';
const rows = await (0, connection_1.query)(`UPDATE faq_categories SET
${filenameCol} = NULL,
${originalCol} = NULL,
updated_at = NOW()
WHERE id = $1 RETURNING *`, [id]);
if (!rows[0])
return null;
return { row: rows[0], removedFilename };
}
async function deleteCategory(id) {
const rows = await (0, connection_1.query)(`DELETE FROM faq_categories WHERE id = $1 RETURNING id`, [id]);
return rows.length > 0;
}
async function listPublicEntries(filters) {
const conditions = ['e.is_active = TRUE', 'c.is_active = TRUE'];
const params = [];
let i = 1;
if (filters.categorySlug) {
conditions.push(`c.slug = $${i++}`);
params.push(filters.categorySlug);
}
if (filters.search?.trim()) {
conditions.push(`(e.question ILIKE $${i} OR e.answer_html ILIKE $${i})`);
params.push(`%${filters.search.trim()}%`);
i++;
}
return (0, connection_1.query)(`SELECT e.*, c.slug AS category_slug, c.name AS category_name
FROM faq_entries e
INNER JOIN faq_categories c ON c.id = e.category_id
WHERE ${conditions.join(' AND ')}
ORDER BY c.sort_order ASC, e.sort_order ASC, e.question ASC`, params);
}
async function listPopularEntries(limit = 5) {
return (0, connection_1.query)(`SELECT e.*, c.slug AS category_slug, c.name AS category_name
FROM faq_entries e
INNER JOIN faq_categories c ON c.id = e.category_id
WHERE e.is_active = TRUE AND c.is_active = TRUE
ORDER BY (e.helpful_yes - e.helpful_no) DESC, e.view_count DESC
LIMIT $1`, [Math.min(limit, 20)]);
}
async function getEntryById(id) {
const rows = await (0, connection_1.query)(`SELECT e.*, c.slug AS category_slug, c.name AS category_name
FROM faq_entries e
INNER JOIN faq_categories c ON c.id = e.category_id
WHERE e.id = $1`, [id]);
return rows[0] ?? null;
}
async function incrementViewCount(id) {
await (0, connection_1.query)(`UPDATE faq_entries SET view_count = view_count + 1 WHERE id = $1`, [id]);
}
async function recordHelpfulVote(faqId, voterKey, helpful) {
const existing = await (0, connection_1.query)(`SELECT helpful FROM faq_helpful_votes WHERE faq_id = $1 AND voter_key = $2`, [faqId, voterKey]);
if (existing[0]) {
if (existing[0].helpful === helpful) {
const counts = await getHelpfulCounts(faqId);
return { ...counts, changed: false };
}
await (0, connection_1.query)(`UPDATE faq_helpful_votes SET helpful = $3, created_at = NOW()
WHERE faq_id = $1 AND voter_key = $2`, [faqId, voterKey, helpful]);
if (existing[0].helpful) {
await (0, connection_1.query)(`UPDATE faq_entries SET helpful_yes = GREATEST(helpful_yes - 1, 0), helpful_no = helpful_no + 1 WHERE id = $1`, [faqId]);
}
else {
await (0, connection_1.query)(`UPDATE faq_entries SET helpful_no = GREATEST(helpful_no - 1, 0), helpful_yes = helpful_yes + 1 WHERE id = $1`, [faqId]);
}
}
else {
await (0, connection_1.query)(`INSERT INTO faq_helpful_votes (faq_id, voter_key, helpful) VALUES ($1, $2, $3)`, [faqId, voterKey, helpful]);
if (helpful) {
await (0, connection_1.query)(`UPDATE faq_entries SET helpful_yes = helpful_yes + 1 WHERE id = $1`, [faqId]);
}
else {
await (0, connection_1.query)(`UPDATE faq_entries SET helpful_no = helpful_no + 1 WHERE id = $1`, [faqId]);
}
}
const counts = await getHelpfulCounts(faqId);
return { ...counts, changed: true };
}
async function getHelpfulCounts(faqId) {
const rows = await (0, connection_1.query)(`SELECT helpful_yes, helpful_no FROM faq_entries WHERE id = $1`, [faqId]);
return {
helpfulYes: rows[0]?.helpful_yes ?? 0,
helpfulNo: rows[0]?.helpful_no ?? 0,
};
}
async function listAdminEntries(filters) {
const conditions = ['1=1'];
const params = [];
let i = 1;
if (filters.categoryId != null) {
conditions.push(`e.category_id = $${i++}`);
params.push(filters.categoryId);
}
if (filters.isActive != null) {
conditions.push(`e.is_active = $${i++}`);
params.push(filters.isActive);
}
if (filters.search?.trim()) {
conditions.push(`(e.question ILIKE $${i} OR e.answer_html ILIKE $${i})`);
params.push(`%${filters.search.trim()}%`);
i++;
}
return (0, connection_1.query)(`SELECT e.*, c.slug AS category_slug, c.name AS category_name,
TRIM(CONCAT(cb.first_name, ' ', cb.last_name)) AS created_by_name,
TRIM(CONCAT(ub.first_name, ' ', ub.last_name)) AS updated_by_name
FROM faq_entries e
INNER JOIN faq_categories c ON c.id = e.category_id
LEFT JOIN users cb ON cb.id = e.created_by
LEFT JOIN users ub ON ub.id = e.updated_by
WHERE ${conditions.join(' AND ')}
ORDER BY c.sort_order ASC, e.sort_order ASC, e.created_at DESC`, params);
}
async function createEntry(input) {
const rows = await (0, connection_1.query)(`INSERT INTO faq_entries (
category_id, question, answer_html, is_active, sort_order, created_by, updated_by
) VALUES ($1, $2, $3, $4, $5, $6, $6) RETURNING *`, [
input.categoryId,
input.question,
input.answerHtml,
input.isActive ?? true,
input.sortOrder ?? 0,
input.userId,
]);
return rows[0];
}
async function updateEntry(id, patch) {
const current = await (0, connection_1.query)(`SELECT * FROM faq_entries WHERE id = $1`, [id]);
if (!current[0])
return null;
const e = current[0];
const rows = await (0, connection_1.query)(`UPDATE faq_entries SET
category_id = $2,
question = $3,
answer_html = $4,
is_active = $5,
sort_order = $6,
updated_by = $7,
updated_at = NOW()
WHERE id = $1 RETURNING *`, [
id,
patch.categoryId ?? e.category_id,
patch.question ?? e.question,
patch.answerHtml ?? e.answer_html,
patch.isActive ?? e.is_active,
patch.sortOrder ?? e.sort_order,
patch.userId ?? e.updated_by,
]);
return rows[0] ?? null;
}
async function deleteEntry(id) {
const rows = await (0, connection_1.query)(`DELETE FROM faq_entries WHERE id = $1 RETURNING id`, [id]);
return rows.length > 0;
}
async function countEntriesInCategory(categoryId) {
const rows = await (0, connection_1.query)(`SELECT COUNT(*)::text AS count FROM faq_entries WHERE category_id = $1`, [categoryId]);
return Number(rows[0]?.count ?? 0);
}
//# sourceMappingURL=faqRepository.js.map