require("dotenv").config({ quiet: true });
const express = require("express");
const OpenAI = require("openai").default;
const Anthropic = require("@anthropic-ai/sdk").default;
const { GoogleGenAI } = require("@google/genai");
const path = require("path");
const fs = require("fs/promises");
const fsSync = require("fs");
const { execFile } = require("child_process");
const { promisify } = require("util");
const multer = require("multer");
const cookieParser = require("cookie-parser");
const JSZip = require("jszip");
const { Pool } = require("pg");
const { v4: uuidv4 } = require("uuid");
const meetingMinutesLib = require("./lib/meeting-minutes");
const meetingAiStore = require("./lib/meeting-minutes-store");
const taskChecklistStore = require("./lib/task-checklist-store");
const parseChecklistFromMinutes = require("./lib/parse-checklist-from-minutes");
const { formatMeetingDateOnly } = require("./lib/meeting-date-format");
const {
normalizeOpsState,
isOpsStateDev,
isOpsStateProd,
isOpsStateSuper,
} = require("./lib/ops-state");
const { fetchOpenGraphImageUrl } = require("./lib/link-preview");
const app = express();
const PORT = process.env.PORT || 8030;
/** 로컬 전용으로만 열 때: HOST=127.0.0.1 (기본은 모든 인터페이스) */
const HOST = process.env.HOST || "0.0.0.0";
/** 강의 목록 페이지당 개수. `.env`에 PAGE_SIZE가 있으면 그 값이 우선(미설정 시 9) */
const PAGE_SIZE = Number(process.env.PAGE_SIZE || 9);
const ADMIN_TOKEN = (process.env.ADMIN_TOKEN || "xavis-admin").trim();
const ENABLE_PPT_THUMBNAIL = process.env.ENABLE_PPT_THUMBNAIL !== "0";
const THUMBNAIL_WIDTH = Number(process.env.THUMBNAIL_WIDTH || 1000);
const THUMBNAIL_MAX_RETRY = Number(process.env.THUMBNAIL_MAX_RETRY || 2);
const THUMBNAIL_RETRY_DELAY_MS = Number(process.env.THUMBNAIL_RETRY_DELAY_MS || 5000);
const THUMBNAIL_EVENT_KEEP = Number(process.env.THUMBNAIL_EVENT_KEEP || 200);
const THUMBNAIL_EVENT_PAGE_SIZE = Number(process.env.THUMBNAIL_EVENT_PAGE_SIZE || 50);
const ENABLE_POSTGRES = process.env.ENABLE_POSTGRES !== "0";
/** 회의 음성 업로드 최대 크기(MB). OpenAI 전사는 요청당 약 25MB이므로 그보다 큰 파일은 lib/meeting-minutes에서 ffmpeg 분할 후 전사 */
const MEETING_AUDIO_MAX_MB_RAW = Number(process.env.MEETING_AUDIO_MAX_MB);
const MEETING_AUDIO_MAX_MB = Number.isFinite(MEETING_AUDIO_MAX_MB_RAW) && MEETING_AUDIO_MAX_MB_RAW > 0
? Math.min(500, Math.max(25, MEETING_AUDIO_MAX_MB_RAW))
: 300;
const MEETING_AUDIO_MAX_BYTES = Math.floor(MEETING_AUDIO_MAX_MB * 1024 * 1024);
const OPENAI_API_KEY = (process.env.OPENAI_API_KEY || "").trim();
/** OpenAI Responses API의 내장 `web_search` 도구 사용. `OPENAI_WEB_SEARCH=0`이면 기존 Chat Completions만 사용 */
const OPENAI_WEB_SEARCH = process.env.OPENAI_WEB_SEARCH !== "0";
const CLAUDE_API_KEY = process.env.CLAUDE_API_KEY || "";
const GENAI_API_KEY = process.env.GENAI_API_KEY || "";
const ROOT_DIR = __dirname;
const DATA_DIR = path.join(ROOT_DIR, "data");
const COMPANY_PROMPTS_PATH = path.join(DATA_DIR, "company-prompts.json");
function loadCompanyPrompts() {
try {
return JSON.parse(fsSync.readFileSync(COMPANY_PROMPTS_PATH, "utf8"));
} catch (e) {
console.error("company-prompts.json load failed:", e.message);
return [];
}
}
function loadAiSuccessStoriesMeta() {
try {
const raw = fsSync.readFileSync(AI_SUCCESS_META_PATH, "utf8");
const arr = JSON.parse(raw);
return Array.isArray(arr) ? arr : [];
} catch {
return [];
}
}
function saveAiSuccessStoriesMeta(list) {
fsSync.mkdirSync(path.dirname(AI_SUCCESS_META_PATH), { recursive: true });
fsSync.mkdirSync(AI_SUCCESS_CONTENT_DIR, { recursive: true });
fsSync.writeFileSync(AI_SUCCESS_META_PATH, JSON.stringify(list, null, 2), "utf8");
}
function loadStoryBodyMarkdown(meta) {
if (!meta) return "";
if (typeof meta.bodyMarkdown === "string" && meta.bodyMarkdown.length) return meta.bodyMarkdown;
if (meta.contentFile) {
const safeName = path.basename(meta.contentFile);
try {
return fsSync.readFileSync(path.join(AI_SUCCESS_CONTENT_DIR, safeName), "utf8");
} catch {
return "";
}
}
return "";
}
function enrichAiSuccessStory(meta) {
return { ...meta, bodyMarkdown: loadStoryBodyMarkdown(meta) };
}
/** 본문 md 파일명: 슬러그 + 생성 시각(ms)으로 파일명 충돌 가능성 완화 */
function buildAiSuccessStoryContentFileName(slug) {
const s = String(slug || "")
.trim()
.toLowerCase()
.replace(/[^a-z0-9\-]/g, "");
const base = s || "story";
return `${base}-${Date.now()}.md`;
}
function filterAiSuccessStories(list, q, tag) {
let out = [...list];
const qLower = (q || "").trim().toLowerCase();
if (qLower) {
out = out.filter((s) => {
return (
(s.title || "").toLowerCase().includes(qLower) ||
(s.excerpt || "").toLowerCase().includes(qLower) ||
(s.department || "").toLowerCase().includes(qLower) ||
(s.tags || []).some((t) => String(t).toLowerCase().includes(qLower))
);
});
}
const tagTrim = (tag || "").trim();
if (tagTrim) {
out = out.filter((s) => (s.tags || []).includes(tagTrim));
}
out.sort((a, b) => {
const da = new Date(a.publishedAt || a.updatedAt || a.createdAt || 0);
const db = new Date(b.publishedAt || b.updatedAt || b.createdAt || 0);
return db - da;
});
return out;
}
function allAiSuccessStoryTags(list) {
const set = new Set();
list.forEach((s) => (s.tags || []).forEach((t) => set.add(t)));
return Array.from(set).sort();
}
function requireAdminApi(req, res, next) {
if (!res.locals.adminMode) {
res.status(403).json({ error: "관리자만 사용할 수 있습니다." });
return;
}
next();
}
const TMP_DIR = path.join(DATA_DIR, "tmp");
const UPLOAD_DIR = path.join(ROOT_DIR, "uploads");
const THUMBNAIL_DIR = path.join(UPLOAD_DIR, "thumbnails");
const SLIDES_DIR = path.join(UPLOAD_DIR, "slides");
const RESOURCES_LECTURE_DIR = path.join(ROOT_DIR, "resources", "lecture");
const RESOURCES_AX_APPLY_DIR = path.join(ROOT_DIR, "resources", "ax-apply");
const AX_APPLY_UPLOAD_DIR = path.join(UPLOAD_DIR, "ax-apply");
const MEETING_MINUTES_UPLOAD_DIR = path.join(UPLOAD_DIR, "meeting-minutes");
const LECTURE_DB_PATH = path.join(DATA_DIR, "lectures.json");
const AX_ASSIGNMENTS_DB_PATH = path.join(DATA_DIR, "ax-assignments.json");
const THUMBNAIL_JOB_DB_PATH = path.join(DATA_DIR, "thumbnail-jobs.json");
const THUMBNAIL_EVENT_DB_PATH = path.join(DATA_DIR, "thumbnail-events.json");
const thumbnailEventsStore = require("./lib/thumbnail-events-store");
const AI_SUCCESS_META_PATH = path.join(DATA_DIR, "ai-success-stories.json");
const AI_SUCCESS_CONTENT_DIR = path.join(DATA_DIR, "ai-success-stories");
const DB_SCHEMA_PATH = path.join(ROOT_DIR, "db", "schema.sql");
const execFileAsync = promisify(execFile);
const thumbnailQueue = [];
const queuedLectureIds = new Set();
let thumbnailWorkerRunning = false;
let pgPool = null;
const readThumbnailJobDb = async () => {
try {
const raw = await fs.readFile(THUMBNAIL_JOB_DB_PATH, "utf-8");
const parsed = JSON.parse(raw);
return Array.isArray(parsed) ? parsed : [];
} catch {
return [];
}
};
const writeThumbnailJobDb = async () => {
const snapshot = thumbnailQueue.map((job) => ({
lectureId: job.lectureId,
force: job.force === true,
reason: job.reason || "manual",
enqueuedAt: job.enqueuedAt || Date.now(),
}));
await fs.writeFile(THUMBNAIL_JOB_DB_PATH, JSON.stringify(snapshot, null, 2), "utf-8");
};
const readThumbnailEventDb = async () =>
thumbnailEventsStore.readThumbnailEvents(pgPool, THUMBNAIL_EVENT_DB_PATH);
const writeThumbnailEventDb = async (events) => {
if (Array.isArray(events) && events.length === 0) {
return thumbnailEventsStore.clearThumbnailEvents(pgPool, THUMBNAIL_EVENT_DB_PATH);
}
throw new Error("썸네일 이벤트는 DB/파일 전체 덮어쓰기를 지원하지 않습니다. clear([])만 사용하세요.");
};
const appendThumbnailEvent = async (payload) =>
thumbnailEventsStore.appendThumbnailEvent(pgPool, THUMBNAIL_EVENT_DB_PATH, THUMBNAIL_EVENT_KEEP, payload);
const getPgConfig = () => ({
host: process.env.DB_HOST,
port: Number(process.env.DB_PORT || 5432),
database: process.env.DB_DATABASE,
user: process.env.DB_USERNAME,
password: process.env.DB_PASSWORD,
ssl: process.env.DB_SSL === "1" ? { rejectUnauthorized: false } : false,
});
const assertPgConfig = () => {
if (!ENABLE_POSTGRES) return;
const config = getPgConfig();
const missing = ["host", "database", "user", "password"].filter((key) => !config[key]);
if (missing.length) {
throw new Error(`PostgreSQL env 누락: ${missing.join(", ")}`);
}
};
const initPostgres = async () => {
if (!ENABLE_POSTGRES) return;
try {
assertPgConfig();
pgPool = new Pool(getPgConfig());
await pgPool.query("SELECT 1");
const schemaSql = await fs.readFile(DB_SCHEMA_PATH, "utf-8");
await pgPool.query(schemaSql);
console.log("PostgreSQL 연결 및 스키마 적용 완료.");
} catch (err) {
console.warn("PostgreSQL 연결 실패, 파일 저장소로 폴백:", err.message);
pgPool = null;
}
};
function getClientIpForAudit(req) {
const xf = (req.headers["x-forwarded-for"] || "").split(",")[0].trim();
if (xf) return xf.slice(0, 45);
const ra = req.socket?.remoteAddress || req.connection?.remoteAddress;
return typeof ra === "string" ? ra.slice(0, 45) : null;
}
async function recordOpsEmailAuthEvent({ email, eventType, req, returnTo = null }) {
if (!pgPool || !email) return;
const e = String(email).trim().toLowerCase().slice(0, 320);
const ua = (req.headers["user-agent"] || "").slice(0, 4000) || null;
const ip = getClientIpForAudit(req);
try {
await pgPool.query(
`INSERT INTO ops_email_auth_events (email, event_type, ip_address, user_agent, return_to)
VALUES ($1, $2, $3, $4, $5)`,
[e, eventType, ip, ua, returnTo ? String(returnTo).slice(0, 2000) : null]
);
} catch (err) {
console.error("[ops-email-auth] record failed:", err.message);
}
}
async function upsertOpsEmailUserOnLogin(email) {
if (!pgPool || !email) return;
const e = String(email).trim().toLowerCase().slice(0, 320);
try {
await pgPool.query(
`INSERT INTO ops_email_users (email, first_seen_at, last_login_at, login_count)
VALUES ($1, NOW(), NOW(), 1)
ON CONFLICT (email) DO UPDATE SET
last_login_at = NOW(),
login_count = ops_email_users.login_count + 1`,
[e]
);
} catch (err) {
console.error("[ops-email-users] upsert failed:", err.message);
}
}
const opsAuth = require("./ops-auth")(DATA_DIR, {
onMagicLinkRequested: async ({ email, returnTo, req }) => {
await recordOpsEmailAuthEvent({
email,
eventType: "magic_link_requested",
req,
returnTo,
});
},
onLoginSuccess: async ({ email, returnTo, req }) => {
await recordOpsEmailAuthEvent({
email,
eventType: "login_success",
req,
returnTo,
});
await upsertOpsEmailUserOnLogin(email);
},
onLogout: async ({ email, req }) => {
if (email) {
await recordOpsEmailAuthEvent({
email,
eventType: "logout",
req,
});
}
},
});
const syncLecturesToPostgres = async (lectures) => {
if (!ENABLE_POSTGRES || !pgPool) return;
const client = await pgPool.connect();
try {
await client.query("BEGIN");
for (const lecture of lectures) {
await client.query(
`INSERT INTO lectures (
id, type, title, description, tags, youtube_url, file_name, original_name,
preview_title, slide_count, thumbnail_url, thumbnail_status, thumbnail_retry_count,
thumbnail_error, thumbnail_updated_at, created_at, list_section, news_url
) VALUES (
$1::uuid, $2, $3, $4, $5::text[], $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18
)
ON CONFLICT (id) DO UPDATE SET
type = EXCLUDED.type,
title = EXCLUDED.title,
description = EXCLUDED.description,
tags = EXCLUDED.tags,
youtube_url = EXCLUDED.youtube_url,
file_name = EXCLUDED.file_name,
original_name = EXCLUDED.original_name,
preview_title = EXCLUDED.preview_title,
slide_count = EXCLUDED.slide_count,
thumbnail_url = EXCLUDED.thumbnail_url,
thumbnail_status = EXCLUDED.thumbnail_status,
thumbnail_retry_count = EXCLUDED.thumbnail_retry_count,
thumbnail_error = EXCLUDED.thumbnail_error,
thumbnail_updated_at = EXCLUDED.thumbnail_updated_at,
list_section = EXCLUDED.list_section,
news_url = EXCLUDED.news_url`,
[
lecture.id,
lecture.type,
lecture.title,
lecture.description || "",
Array.isArray(lecture.tags) ? lecture.tags : [],
lecture.youtubeUrl || null,
lecture.fileName || null,
lecture.originalName || null,
lecture.previewTitle || null,
typeof lecture.slideCount === "number" ? lecture.slideCount : 0,
lecture.thumbnailUrl || null,
lecture.thumbnailStatus || "pending",
typeof lecture.thumbnailRetryCount === "number" ? lecture.thumbnailRetryCount : 0,
lecture.thumbnailError || null,
lecture.thumbnailUpdatedAt ? new Date(lecture.thumbnailUpdatedAt) : null,
lecture.createdAt ? new Date(lecture.createdAt) : new Date(),
lecture.listSection || "learning",
lecture.newsUrl || null,
]
);
}
if (lectures.length > 0) {
await client.query("DELETE FROM lectures WHERE id <> ALL($1::uuid[])", [lectures.map((lecture) => lecture.id)]);
} else {
await client.query("DELETE FROM lectures");
}
await client.query("COMMIT");
} catch (error) {
await client.query("ROLLBACK");
throw error;
} finally {
client.release();
}
};
const escapeCsv = (value) => {
const str = String(value ?? "");
if (str.includes('"') || str.includes(",") || str.includes("\n")) {
return `"${str.replace(/"/g, '""')}"`;
}
return str;
};
const buildEventFilter = (query) => {
const eventType = (query.eventType || "all").toString();
const lectureId = (query.lectureId || "").toString().trim();
const reason = (query.reason || "").toString().trim();
const from = (query.from || "").toString().trim();
const to = (query.to || "").toString().trim();
const fromMs = from ? new Date(from).getTime() : null;
const toMs = to ? new Date(`${to}T23:59:59.999`).getTime() : null;
return {
eventType,
lectureId,
reason,
from,
to,
matches: (evt) => {
if (eventType !== "all" && evt.type !== eventType) return false;
if (lectureId && !String(evt.lectureId || "").includes(lectureId)) return false;
if (reason && !String(evt.reason || "").includes(reason)) return false;
const atMs = new Date(evt.at || "").getTime();
if (Number.isFinite(fromMs) && Number.isFinite(atMs) && atMs < fromMs) return false;
if (Number.isFinite(toMs) && Number.isFinite(atMs) && atMs > toMs) return false;
return true;
},
};
};
const storage = multer.diskStorage({
destination: (_, __, cb) => cb(null, UPLOAD_DIR),
filename: (_, file, cb) => {
const ext = (path.extname(file.originalname) || ".pptx").toLowerCase();
const allowed = [".pptx", ".pdf"];
const finalExt = allowed.includes(ext) ? ext : ".pptx";
cb(null, `${Date.now()}-${uuidv4()}${finalExt}`);
},
});
const upload = multer({
storage,
limits: { fileSize: 50 * 1024 * 1024 },
fileFilter: (_, file, cb) => {
const ext = path.extname(file.originalname).toLowerCase();
if (ext !== ".pptx" && ext !== ".pdf") {
cb(new Error("PDF(.pdf) 또는 PowerPoint(.pptx) 파일만 업로드할 수 있습니다."));
return;
}
cb(null, true);
},
});
const LECTURE_VIDEO_ALLOWED_EXT = [".mp4", ".webm", ".mov"];
const lectureVideoMaxBytes =
Math.max(1, Number(process.env.LECTURE_VIDEO_MAX_MB) || 500) * 1024 * 1024;
const videoStorage = multer.diskStorage({
destination: (_, __, cb) => cb(null, UPLOAD_DIR),
filename: (_, file, cb) => {
const ext = (path.extname(file.originalname) || ".mp4").toLowerCase();
const finalExt = LECTURE_VIDEO_ALLOWED_EXT.includes(ext) ? ext : ".mp4";
cb(null, `${Date.now()}-${uuidv4()}${finalExt}`);
},
});
const uploadVideo = multer({
storage: videoStorage,
limits: { fileSize: lectureVideoMaxBytes },
fileFilter: (_, file, cb) => {
const ext = path.extname(file.originalname).toLowerCase();
if (!LECTURE_VIDEO_ALLOWED_EXT.includes(ext)) {
cb(new Error("동영상은 MP4, WebM, MOV 형식만 업로드할 수 있습니다."));
return;
}
cb(null, true);
},
});
const axApplyStorage = multer.diskStorage({
destination: (_, __, cb) => {
fsSync.mkdirSync(AX_APPLY_UPLOAD_DIR, { recursive: true });
cb(null, AX_APPLY_UPLOAD_DIR);
},
filename: (_, file, cb) => {
const ext = (path.extname(file.originalname) || ".docx").toLowerCase();
const finalExt = ext === ".docx" || ext === ".doc" ? ".docx" : ".docx";
cb(null, `${Date.now()}-${uuidv4()}${finalExt}`);
},
});
const uploadAxApply = multer({
storage: axApplyStorage,
limits: { fileSize: 10 * 1024 * 1024 },
fileFilter: (_, file, cb) => {
const ext = path.extname(file.originalname).toLowerCase();
if (ext !== ".docx" && ext !== ".doc") {
cb(new Error("Word 문서(.docx, .doc) 파일만 업로드할 수 있습니다."));
return;
}
cb(null, true);
},
});
const AI_SUCCESS_PUBLIC_PDF_DIR = path.join(ROOT_DIR, "public", "resources", "ai-success");
const aiSuccessPdfStorage = multer.diskStorage({
destination: (_, __, cb) => {
fsSync.mkdirSync(AI_SUCCESS_PUBLIC_PDF_DIR, { recursive: true });
cb(null, AI_SUCCESS_PUBLIC_PDF_DIR);
},
filename: (_, __, cb) => {
cb(null, `${Date.now()}-${uuidv4()}.pdf`);
},
});
const uploadAiSuccessPdf = multer({
storage: aiSuccessPdfStorage,
limits: { fileSize: 50 * 1024 * 1024 },
fileFilter: (_, file, cb) => {
const ext = path.extname(file.originalname).toLowerCase();
if (ext !== ".pdf") {
cb(new Error("PDF(.pdf) 파일만 업로드할 수 있습니다."));
return;
}
cb(null, true);
},
});
const meetingMinutesAudioStorage = multer.diskStorage({
destination: (req, _, cb) => {
const email = req.meetingUserEmail || "unknown";
const safe = String(email).replace(/[^a-zA-Z0-9@._-]/g, "_").slice(0, 200);
const dir = path.join(MEETING_MINUTES_UPLOAD_DIR, safe);
fsSync.mkdirSync(dir, { recursive: true });
cb(null, dir);
},
filename: (req, file, cb) => {
const email = req.meetingUserEmail || "user";
const safe = String(email).replace(/[^a-zA-Z0-9@._-]/g, "_").slice(0, 200);
const now = new Date();
const p = (n) => String(n).padStart(2, "0");
const yyyyMMdd = `${now.getFullYear()}${p(now.getMonth() + 1)}${p(now.getDate())}`;
const HHmmss = `${p(now.getHours())}${p(now.getMinutes())}${p(now.getSeconds())}`;
const ext = (path.extname(file.originalname) || ".webm").toLowerCase();
cb(null, `${safe}_${yyyyMMdd}_${HHmmss}${ext}`);
},
});
const uploadMeetingAudio = multer({
storage: meetingMinutesAudioStorage,
limits: { fileSize: MEETING_AUDIO_MAX_BYTES },
fileFilter: (_, file, cb) => {
const ext = path.extname(file.originalname).toLowerCase();
const allowed = [".mp3", ".mp4", ".mpeg", ".mpga", ".m4a", ".wav", ".webm", ".ogg", ".flac"];
if (!allowed.includes(ext)) {
cb(new Error(`지원 음성 형식: ${allowed.join(", ")}`));
return;
}
cb(null, true);
},
});
const mapRowToAxAssignment = (row) => ({
id: row.id,
department: row.department || "",
name: row.name || "",
employeeId: row.employee_id || "",
position: row.position || "",
phone: row.phone || "",
email: row.email || "",
workProcessDescription: row.work_process_description || "",
painPoint: row.pain_point || "",
currentTimeSpent: row.current_time_spent || "",
errorRateBefore: row.error_rate_before || "",
collaborationDepts: row.collaboration_depts || "",
reasonToSolve: row.reason_to_solve || "",
aiExpectation: row.ai_expectation || "",
outputType: row.output_type || "",
automationLevel: row.automation_level || "",
dataReadiness: row.data_readiness || "",
dataLocation: row.data_location || "",
personalInfo: row.personal_info || "",
dataQuality: row.data_quality || "",
dataCount: row.data_count || "",
dataTypes: Array.isArray(row.data_types) ? row.data_types : [],
timeReduction: row.time_reduction || "",
errorReduction: row.error_reduction || "",
volumeIncrease: row.volume_increase || "",
costReduction: row.cost_reduction || "",
responseTime: row.response_time || "",
otherMetrics: row.other_metrics || "",
annualSavings: row.annual_savings || "",
laborReplacement: row.labor_replacement || "",
revenueIncrease: row.revenue_increase || "",
otherEffects: row.other_effects || "",
qualitativeEffects: Array.isArray(row.qualitative_effects) ? row.qualitative_effects : [],
techStack: Array.isArray(row.tech_stack) ? row.tech_stack : [],
risks: Array.isArray(row.risks) ? row.risks : [],
riskDetail: row.risk_detail || "",
participationPledge: row.participation_pledge === true,
applicationFile: row.application_file || "",
status: row.status || "신청",
createdAt: row.created_at ? new Date(row.created_at).toISOString() : new Date().toISOString(),
updatedAt: row.updated_at ? new Date(row.updated_at).toISOString() : new Date().toISOString(),
});
const readAxAssignmentsDb = async () => {
if (pgPool) {
try {
const res = await pgPool.query(
"SELECT id, department, name, employee_id, position, phone, email, work_process_description, pain_point, current_time_spent, error_rate_before, collaboration_depts, reason_to_solve, ai_expectation, output_type, automation_level, data_readiness, data_location, personal_info, data_quality, data_count, data_types, time_reduction, error_reduction, volume_increase, cost_reduction, response_time, other_metrics, annual_savings, labor_replacement, revenue_increase, other_effects, qualitative_effects, tech_stack, risks, risk_detail, participation_pledge, application_file, status, created_at, updated_at FROM ax_assignments ORDER BY created_at DESC"
);
return (res.rows || []).map(mapRowToAxAssignment);
} catch (err) {
console.error("readAxAssignmentsDb from PostgreSQL failed:", err.message);
return [];
}
}
try {
const raw = await fs.readFile(AX_ASSIGNMENTS_DB_PATH, "utf-8");
const parsed = JSON.parse(raw);
return Array.isArray(parsed) ? parsed : [];
} catch {
return [];
}
};
const readAxAssignmentsByCredentials = async (department, name, email) => {
const d = (department || "").trim().toLowerCase();
const n = (name || "").trim().toLowerCase();
const e = (email || "").trim().toLowerCase();
if (!d || !n || !e) return [];
const all = await readAxAssignmentsDb();
return all.filter(
(a) =>
(a.department || "").trim().toLowerCase() === d &&
(a.name || "").trim().toLowerCase() === n &&
(a.email || "").trim().toLowerCase() === e
);
};
const isSampleRecord = (a) =>
(a?.department || "").trim() === "샘플" && (a?.name || "").trim() === "데이터";
const ensureStringArray = (val) => {
if (val === "" || val === null || val === undefined) return [];
if (Array.isArray(val)) return val.filter((x) => typeof x === "string");
if (typeof val === "string" && val.trim()) return val.split(",").map((s) => s.trim()).filter(Boolean);
return [];
};
const toPgArray = (arr) => {
if (arr === "" || arr === null || arr === undefined) return null;
const a = ensureStringArray(arr);
return a.length > 0 ? a : null;
};
const readAxAssignmentById = async (id) => {
if (!id) return null;
if (pgPool) {
try {
const res = await pgPool.query(
"SELECT id, department, name, employee_id, position, phone, email, work_process_description, pain_point, current_time_spent, error_rate_before, collaboration_depts, reason_to_solve, ai_expectation, output_type, automation_level, data_readiness, data_location, personal_info, data_quality, data_count, data_types, time_reduction, error_reduction, volume_increase, cost_reduction, response_time, other_metrics, annual_savings, labor_replacement, revenue_increase, other_effects, qualitative_effects, tech_stack, risks, risk_detail, participation_pledge, application_file, status, created_at, updated_at FROM ax_assignments WHERE id = $1",
[id]
);
return res.rows?.[0] ? mapRowToAxAssignment(res.rows[0]) : null;
} catch {
return null;
}
}
try {
const raw = await fs.readFile(AX_ASSIGNMENTS_DB_PATH, "utf-8");
const parsed = JSON.parse(raw);
const list = Array.isArray(parsed) ? parsed : [];
const found = list.find((a) => a.id === id);
return found ? { ...found, applicationFile: found.applicationFile || found.application_file || "" } : null;
} catch {
return null;
}
};
async function updateAxAssignmentDb(id, data) {
const existing = await readAxAssignmentById(id);
if (!existing) return null;
const existingAppFile = (existing.applicationFile || existing.application_file || "").trim();
const appFile = (data.applicationFile || data.application_file || "").trim();
const finalAppFile = appFile || existingAppFile;
const cleanArr = (v) => (v === "" || typeof v === "string" ? undefined : v);
const dataTypes = cleanArr(data.dataTypes ?? data.data_types) ?? existing.dataTypes;
const qualitativeEffects = cleanArr(data.qualitativeEffects ?? data.qualitative_effects) ?? existing.qualitativeEffects;
const techStack = cleanArr(data.techStack ?? data.techStackStr ?? data.tech_stack) ?? existing.techStack;
const risks = cleanArr(data.risks ?? data.risksStr ?? data.risks) ?? existing.risks;
const row = {
id,
department: ((data.department ?? existing.department) || "").trim(),
name: ((data.name ?? existing.name) || "").trim(),
employeeId: ((data.employeeId ?? data.employee_id ?? existing.employeeId) || "").trim(),
position: ((data.position ?? existing.position) || "").trim(),
phone: ((data.phone ?? existing.phone) || "").trim(),
email: ((data.email ?? existing.email) || "").trim(),
workProcessDescription: ((data.workProcessDescription ?? data.work_process_description ?? existing.workProcessDescription) || "").trim(),
painPoint: ((data.painPoint ?? data.pain_point ?? existing.painPoint) || "").trim(),
currentTimeSpent: ((data.currentTimeSpent ?? data.current_time_spent ?? existing.currentTimeSpent) || "").trim(),
errorRateBefore: ((data.errorRateBefore ?? data.error_rate_before ?? existing.errorRateBefore) || "").trim(),
collaborationDepts: ((data.collaborationDepts ?? data.collaboration_depts ?? existing.collaborationDepts) || "").trim(),
reasonToSolve: ((data.reasonToSolve ?? data.reason_to_solve ?? existing.reasonToSolve) || "").trim(),
aiExpectation: ((data.aiExpectation ?? data.ai_expectation ?? existing.aiExpectation) || "").trim(),
outputType: ((data.outputType ?? data.output_type ?? existing.outputType) || "").trim(),
automationLevel: ((data.automationLevel ?? data.automation_level ?? existing.automationLevel) || "").trim(),
dataReadiness: ((data.dataReadiness ?? data.data_readiness ?? existing.dataReadiness) || "").trim(),
dataLocation: ((data.dataLocation ?? data.data_location ?? existing.dataLocation) || "").trim(),
personalInfo: ((data.personalInfo ?? data.personal_info ?? existing.personalInfo) || "").trim(),
dataQuality: ((data.dataQuality ?? data.data_quality ?? existing.dataQuality) || "").trim(),
dataCount: ((data.dataCount ?? data.data_count ?? existing.dataCount) || "").trim(),
dataTypes: toPgArray(dataTypes),
timeReduction: ((data.timeReduction ?? data.time_reduction ?? existing.timeReduction) || "").trim(),
errorReduction: ((data.errorReduction ?? data.error_reduction ?? existing.errorReduction) || "").trim(),
volumeIncrease: ((data.volumeIncrease ?? data.volume_increase ?? existing.volumeIncrease) || "").trim(),
costReduction: ((data.costReduction ?? data.cost_reduction ?? existing.costReduction) || "").trim(),
responseTime: ((data.responseTime ?? data.response_time ?? existing.responseTime) || "").trim(),
otherMetrics: ((data.otherMetrics ?? data.other_metrics ?? existing.otherMetrics) || "").trim(),
annualSavings: ((data.annualSavings ?? data.annual_savings ?? existing.annualSavings) || "").trim(),
laborReplacement: ((data.laborReplacement ?? data.labor_replacement ?? existing.laborReplacement) || "").trim(),
revenueIncrease: ((data.revenueIncrease ?? data.revenue_increase ?? existing.revenueIncrease) || "").trim(),
otherEffects: ((data.otherEffects ?? data.other_effects ?? existing.otherEffects) || "").trim(),
qualitativeEffects: toPgArray(qualitativeEffects),
techStack: toPgArray(techStack),
risks: toPgArray(risks),
riskDetail: ((data.riskDetail ?? data.risk_detail ?? existing.riskDetail) || "").trim(),
participationPledge: data.participationPledge === true || data.participation_pledge === true,
applicationFile: finalAppFile,
status: existing.status || "신청",
createdAt: existing.createdAt,
updatedAt: new Date().toISOString(),
};
if (pgPool) {
const toArrayLiteralSafe = (v) => {
if (v === "" || (typeof v === "string") || v == null) return "'{}'";
const arr = Array.isArray(v) ? v.filter((x) => typeof x === "string") : [];
if (arr.length === 0) return "'{}'";
const escaped = arr.map((s) => `"${String(s).replace(/\\/g, "\\\\").replace(/"/g, '\\"')}"`);
return `'{${escaped.join(",")}}'`;
};
const dataTypesLit = toArrayLiteralSafe(row.dataTypes);
const qualitativeLit = toArrayLiteralSafe(row.qualitativeEffects);
const techStackLit = toArrayLiteralSafe(row.techStack);
const risksLit = toArrayLiteralSafe(row.risks);
try {
await pgPool.query(
`UPDATE ax_assignments SET
department = $2, name = $3, employee_id = $4, position = $5, phone = $6, email = $7,
work_process_description = $8, pain_point = $9, current_time_spent = $10, error_rate_before = $11,
collaboration_depts = $12, reason_to_solve = $13, ai_expectation = $14, output_type = $15, automation_level = $16,
data_readiness = $17, data_location = $18, personal_info = $19, data_quality = $20, data_count = $21, data_types = ${dataTypesLit}::text[],
time_reduction = $22, error_reduction = $23, volume_increase = $24, cost_reduction = $25, response_time = $26,
other_metrics = $27, annual_savings = $28, labor_replacement = $29, revenue_increase = $30, other_effects = $31,
qualitative_effects = ${qualitativeLit}::text[], tech_stack = ${techStackLit}::text[], risks = ${risksLit}::text[],
risk_detail = $32, participation_pledge = $33, application_file = $34, updated_at = $35
WHERE id = $1::uuid`,
[
row.id, row.department, row.name, row.employeeId, row.position, row.phone, row.email,
row.workProcessDescription, row.painPoint, row.currentTimeSpent, row.errorRateBefore,
row.collaborationDepts, row.reasonToSolve, row.aiExpectation, row.outputType, row.automationLevel,
row.dataReadiness, row.dataLocation, row.personalInfo, row.dataQuality, row.dataCount,
row.timeReduction, row.errorReduction, row.volumeIncrease, row.costReduction, row.responseTime,
row.otherMetrics, row.annualSavings, row.laborReplacement, row.revenueIncrease, row.otherEffects,
row.riskDetail, row.participationPledge, row.applicationFile || null,
new Date(row.updatedAt),
]
);
} catch (err) {
console.error("updateAxAssignmentDb failed:", err.message);
throw err;
}
} else {
const raw = await fs.readFile(AX_ASSIGNMENTS_DB_PATH, "utf-8");
const list = Array.isArray(JSON.parse(raw)) ? JSON.parse(raw) : [];
const idx = list.findIndex((a) => a.id === id);
if (idx >= 0) {
list[idx] = { ...list[idx], ...row };
await fs.writeFile(AX_ASSIGNMENTS_DB_PATH, JSON.stringify(list, null, 2), "utf-8");
}
}
return row;
};
const createAxAssignmentDb = async (data) => {
const id = uuidv4();
const now = new Date().toISOString();
const cleanArr = (v) => (v === "" || typeof v === "string" ? undefined : v);
const dataTypes = cleanArr(data.dataTypes ?? data.data_types);
const qualitativeEffects = cleanArr(data.qualitativeEffects ?? data.qualitative_effects);
const techStack = cleanArr(data.techStack ?? data.techStackStr ?? data.tech_stack);
const risks = cleanArr(data.risks ?? data.risksStr);
const row = {
id,
department: (data.department || "").trim(),
name: (data.name || "").trim(),
employeeId: (data.employeeId || data.employee_id || "").trim(),
position: (data.position || "").trim(),
phone: (data.phone || "").trim(),
email: (data.email || "").trim(),
workProcessDescription: (data.workProcessDescription || data.work_process_description || "").trim(),
painPoint: (data.painPoint || data.pain_point || "").trim(),
currentTimeSpent: (data.currentTimeSpent || data.current_time_spent || "").trim(),
errorRateBefore: (data.errorRateBefore || data.error_rate_before || "").trim(),
collaborationDepts: (data.collaborationDepts || data.collaboration_depts || "").trim(),
reasonToSolve: (data.reasonToSolve || data.reason_to_solve || "").trim(),
aiExpectation: (data.aiExpectation || data.ai_expectation || "").trim(),
outputType: (data.outputType || data.output_type || "").trim(),
automationLevel: (data.automationLevel || data.automation_level || "").trim(),
dataReadiness: (data.dataReadiness || data.data_readiness || "").trim(),
dataLocation: (data.dataLocation || data.data_location || "").trim(),
personalInfo: (data.personalInfo || data.personal_info || "").trim(),
dataQuality: (data.dataQuality || data.data_quality || "").trim(),
dataCount: (data.dataCount || data.data_count || "").trim(),
dataTypes: toPgArray(dataTypes),
timeReduction: (data.timeReduction || data.time_reduction || "").trim(),
errorReduction: (data.errorReduction || data.error_reduction || "").trim(),
volumeIncrease: (data.volumeIncrease || data.volume_increase || "").trim(),
costReduction: (data.costReduction || data.cost_reduction || "").trim(),
responseTime: (data.responseTime || data.response_time || "").trim(),
otherMetrics: (data.otherMetrics || data.other_metrics || "").trim(),
annualSavings: (data.annualSavings || data.annual_savings || "").trim(),
laborReplacement: (data.laborReplacement || data.labor_replacement || "").trim(),
revenueIncrease: (data.revenueIncrease || data.revenue_increase || "").trim(),
otherEffects: (data.otherEffects || data.other_effects || "").trim(),
qualitativeEffects: toPgArray(qualitativeEffects),
techStack: toPgArray(techStack),
risks: toPgArray(risks),
riskDetail: (data.riskDetail || data.risk_detail || "").trim(),
participationPledge: data.participationPledge === true || data.participation_pledge === true,
applicationFile: (data.applicationFile || data.application_file || "").trim(),
status: "신청",
createdAt: now,
updatedAt: now,
};
const toSafeArrayParam = (v) => {
if (v === "" || (typeof v === "string") || v === undefined) return null;
if (!Array.isArray(v)) return null;
const filtered = v.filter((x) => typeof x === "string");
return filtered.length > 0 ? filtered : null;
};
if (pgPool) {
const toArrayLiteralSafe = (v) => {
if (v === "" || (typeof v === "string") || v == null) return "'{}'";
const arr = Array.isArray(v) ? v.filter((x) => typeof x === "string") : [];
if (arr.length === 0) return "'{}'";
const escaped = arr.map((s) => `"${String(s).replace(/\\/g, "\\\\").replace(/"/g, '\\"')}"`);
return `'{${escaped.join(",")}}'`;
};
const dataTypesLit = toArrayLiteralSafe(row.dataTypes);
const qualitativeLit = toArrayLiteralSafe(row.qualitativeEffects);
const techStackLit = toArrayLiteralSafe(row.techStack);
const risksLit = toArrayLiteralSafe(row.risks);
try {
await pgPool.query(
`INSERT INTO ax_assignments (
id, department, name, employee_id, position, phone, email,
work_process_description, pain_point, current_time_spent, error_rate_before,
collaboration_depts, reason_to_solve, ai_expectation, output_type, automation_level,
data_readiness, data_location, personal_info, data_quality, data_count, data_types,
time_reduction, error_reduction, volume_increase, cost_reduction, response_time,
other_metrics, annual_savings, labor_replacement, revenue_increase, other_effects,
qualitative_effects, tech_stack, risks, risk_detail, participation_pledge, application_file, status,
created_at, updated_at
) VALUES (
$1::uuid, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16,
$17, $18, $19, $20, $21, ${dataTypesLit}::text[], $22, $23, $24, $25, $26, $27, $28, $29, $30,
$31, ${qualitativeLit}::text[], ${techStackLit}::text[], ${risksLit}::text[], $32, $33, $34, $35, $36, $37
)`,
[
row.id, row.department, row.name, row.employeeId, row.position, row.phone, row.email,
row.workProcessDescription, row.painPoint, row.currentTimeSpent, row.errorRateBefore,
row.collaborationDepts, row.reasonToSolve, row.aiExpectation, row.outputType, row.automationLevel,
row.dataReadiness, row.dataLocation, row.personalInfo, row.dataQuality, row.dataCount,
row.timeReduction, row.errorReduction, row.volumeIncrease, row.costReduction, row.responseTime,
row.otherMetrics, row.annualSavings, row.laborReplacement, row.revenueIncrease, row.otherEffects,
row.riskDetail, row.participationPledge, row.applicationFile || null, row.status,
new Date(row.createdAt), new Date(row.updatedAt),
]
);
} catch (err) {
console.error("createAxAssignmentDb failed:", err.message);
throw err;
}
} else {
const list = await readAxAssignmentsDb();
list.unshift(row);
await fs.writeFile(AX_ASSIGNMENTS_DB_PATH, JSON.stringify(list, null, 2), "utf-8");
}
return row;
};
const deleteAxAssignmentDb = async (id) => {
if (!id) return false;
if (pgPool) {
try {
const res = await pgPool.query("DELETE FROM ax_assignments WHERE id = $1::uuid", [id]);
return (res.rowCount || 0) > 0;
} catch (err) {
console.error("deleteAxAssignmentDb failed:", err.message);
throw err;
}
}
try {
const raw = await fs.readFile(AX_ASSIGNMENTS_DB_PATH, "utf-8");
const list = Array.isArray(JSON.parse(raw)) ? JSON.parse(raw) : [];
const idx = list.findIndex((a) => a.id === id);
if (idx < 0) return false;
list.splice(idx, 1);
await fs.writeFile(AX_ASSIGNMENTS_DB_PATH, JSON.stringify(list, null, 2), "utf-8");
return true;
} catch {
return false;
}
};
app.set("view engine", "ejs");
app.set("views", path.join(ROOT_DIR, "views"));
app.use(express.urlencoded({ extended: true }));
app.use(express.json());
app.use(cookieParser());
const ADMIN_COOKIE_NAME = "admin_session";
const ADMIN_COOKIE_MAX_AGE = 24 * 60 * 60 * 1000; // 24시간
app.use((req, res, next) => {
const cookieToken = req.cookies?.[ADMIN_COOKIE_NAME] || "";
res.locals.adminMode =
ADMIN_TOKEN.length > 0 && cookieToken.length > 0 && cookieToken === ADMIN_TOKEN;
next();
});
/** GPT 채팅: SUPER 전면 허용. DEV는 관리자 토큰 세션. PROD는 이메일(OPS) 인증 세션만 */
function isChatGptAllowed(req, res) {
if (isOpsStateSuper()) return true;
if (isOpsStateDev() && res.locals.adminMode) return true;
if (isOpsStateProd() && res.locals.opsUserEmail) return true;
return false;
}
/** AI 성공 사례 상세: DEV일 때만 관리자 모드로 제한. PROD·SUPER는 상세 열람 가능 */
function isAiSuccessStoryDetailAllowed(req, res) {
if (!isOpsStateDev()) return true;
return !!res.locals.adminMode;
}
/** 과제신청: DEV에서만 관리자 제한. PROD·SUPER는 제한 없음 */
function isAxApplyDownloadSubmitAllowed(req, res) {
if (!isOpsStateDev()) return true;
return !!res.locals.adminMode;
}
/** AI 탐색(/ai-explore): DEV·비관리자일 때 프롬프트 카드만 사용 가능 */
function isAiExploreDevGuestRestricted(req, res) {
return isOpsStateDev() && !res.locals.adminMode;
}
/** OPS 이메일 세션, DEV+관리자(MEETING_DEV_EMAIL), SUPER(데모 이메일) — 회의록 AI */
function getMeetingMinutesUserEmail(req, res) {
if (res.locals.opsUserEmail) return String(res.locals.opsUserEmail).trim().toLowerCase();
if (isOpsStateDev() && res.locals.adminMode) {
return (process.env.MEETING_DEV_EMAIL || "dev@xavis.co.kr").trim().toLowerCase();
}
if (isOpsStateSuper()) {
return (process.env.MEETING_SUPER_EMAIL || process.env.MEETING_DEV_EMAIL || "demo@xavis.local")
.trim()
.toLowerCase();
}
return null;
}
const MEETING_MINUTES_ALLOWED_MODELS = new Set(["gpt-5-mini", "gpt-5.4"]);
/** YYYY-MM-DD 검증, 실패 시 { error } */
function parseMeetingDateIso(raw) {
if (raw == null || String(raw).trim() === "") return { value: null };
const s = String(raw).trim().slice(0, 10);
if (!/^\d{4}-\d{2}-\d{2}$/.test(s)) return { error: "날짜는 YYYY-MM-DD 형식이어야 합니다." };
const [y, mo, d] = s.split("-").map(Number);
const dt = new Date(y, mo - 1, d);
if (dt.getFullYear() !== y || dt.getMonth() !== mo - 1 || dt.getDate() !== d) return { error: "유효하지 않은 날짜입니다." };
return { value: s };
}
function requireMeetingMinutesEmail(req, res, next) {
const email = getMeetingMinutesUserEmail(req, res);
if (!email) {
res.status(401).json({ error: "이메일 로그인이 필요합니다." });
return;
}
req.meetingUserEmail = email;
next();
}
async function ensureMeetingUserAndDefaultPrompt(email) {
await meetingAiStore.ensureUserAndDefaultPrompt(pgPool, email);
}
function mapRowToMeetingPrompt(row) {
if (!row) return null;
return {
id: row.id,
userEmail: row.user_email,
includeTitleLine: row.include_title_line !== false,
includeAttendees: row.include_attendees !== false,
includeSummary: row.include_summary !== false,
includeActionItems: row.include_action_items !== false,
includeChecklist: true,
customInstructions: row.custom_instructions || "",
createdAt: row.created_at,
updatedAt: row.updated_at,
};
}
function mapRowToMeeting(row) {
if (!row) return null;
return {
id: row.id,
userEmail: row.user_email,
title: row.title || "",
sourceText: row.source_text || "",
transcriptText: row.transcript_text || "",
generatedMinutes: meetingMinutesLib.prepareMeetingMinutesForApi(row.generated_minutes || ""),
audioFilePath: row.audio_file_path || "",
audioOriginalName: row.audio_original_name || "",
chatModel: row.chat_model || "gpt-5-mini",
transcriptionModel: row.transcription_model || null,
meetingDate: row.meeting_date != null ? formatMeetingDateOnly(row.meeting_date) : null,
createdAt: row.created_at ? new Date(row.created_at).toISOString() : null,
updatedAt: row.updated_at ? new Date(row.updated_at).toISOString() : null,
checklistSnapshot: row.checklist_snapshot != null ? row.checklist_snapshot : null,
summaryText: row.summary_text != null && String(row.summary_text).trim() ? String(row.summary_text).trim() : "",
};
}
/**
* 규칙 기반 액션(번호)·체크리스트를 우선하고 LLM 추출로 보완 (제목 기준 중복 제거)
* @param {string} markdown
* @param {Array<{ title: string, detail?: string, assignee?: string|null, due_note?: string|null, completed?: boolean }>} llmItems
*/
function mergeLlmAndRuleWorkItemsForSync(markdown, llmItems) {
const rule = parseChecklistFromMinutes.parseAllRuleBasedWorkItems(markdown);
const seen = new Set();
/** @type {Array<{ title: string, detail: string, assignee: string|null, due_note: string|null, completed: boolean }>} */
const out = [];
function push(arr) {
for (const it of arr) {
const t = (it.title || "").trim().toLowerCase();
if (!t) continue;
if (seen.has(t)) continue;
seen.add(t);
out.push({
title: String(it.title).trim(),
detail: String(it.detail != null ? it.detail : "").trim(),
assignee: it.assignee != null && String(it.assignee).trim() ? String(it.assignee).trim() : null,
due_note: it.due_note != null && String(it.due_note).trim() ? String(it.due_note).trim() : null,
completed: !!it.completed,
});
}
}
push(rule);
push(llmItems || []);
return out;
}
/**
* 회의록 저장 직후: 규칙(액션·체크리스트) + LLM JSON 추출 병합 → 스냅샷 저장 → 업무 체크리스트 행 삽입
*/
async function syncAutoChecklistFromMeetingMinutes(openai, { pgPool, email, meetingId, generatedMinutes, uiModel }) {
if ((process.env.MEETING_AUTO_CHECKLIST || "1").trim() === "0") {
return { imported: 0, disabled: true };
}
const maxChars = Number(process.env.MEETING_CHECKLIST_EXTRACT_MAX_CHARS || 24000);
let slice = String(generatedMinutes || "").trim();
if (!slice) return { imported: 0 };
if (slice.length > maxChars) slice = slice.slice(slice.length - maxChars);
/** @type {Array<{ title: string, detail?: string, assignee?: string|null, due_note?: string|null, completed?: boolean }>} */
let llmItems = [];
let extractError = null;
try {
llmItems = await meetingMinutesLib.extractChecklistStructured(openai, {
minutesMarkdown: slice,
uiModel,
resolveApiModel: resolveOpenAiApiModel,
});
} catch (e) {
extractError = e?.response?.data?.error?.message || e?.message || "extract failed";
console.warn("[meeting] checklist extract:", extractError);
}
const merged = mergeLlmAndRuleWorkItemsForSync(slice, llmItems || []);
const snapshot = {
items: merged,
extractedAt: new Date().toISOString(),
extractor: "rule-actions-checklist+openai-json",
llmItemCount: (llmItems || []).length,
mergedItemCount: merged.length,
...(extractError ? { extractError } : {}),
};
try {
await meetingAiStore.updateMeetingChecklistSnapshot(pgPool, meetingId, email, snapshot);
} catch (e) {
console.warn("[meeting] checklist_snapshot update:", e?.message);
}
if (!merged.length) {
return { imported: 0, snapshot, ...(extractError ? { extractError } : {}) };
}
try {
const inserted = await taskChecklistStore.insertImportedBatch(pgPool, email, meetingId, merged);
return { imported: inserted.length, snapshot, ...(extractError ? { extractError } : {}) };
} catch (e) {
console.warn("[meeting] checklist insert batch:", e?.message);
return { imported: 0, snapshot, importError: e?.message, ...(extractError ? { extractError } : {}) };
}
}
app.use("/public", express.static(path.join(ROOT_DIR, "public")));
app.get("/favicon.ico", (req, res) => {
res.type("image/x-icon");
res.sendFile(path.join(ROOT_DIR, "public", "favicon.ico"));
});
/** 채팅 마크다운 뷰어(marked + DOMPurify) — node_modules에서만 제공 */
app.use("/vendor/marked", express.static(path.join(ROOT_DIR, "node_modules/marked/lib")));
app.use("/vendor/dompurify", express.static(path.join(ROOT_DIR, "node_modules/dompurify/dist")));
app.use("/uploads", express.static(UPLOAD_DIR));
app.get("/resources/ax-apply/AX_과제_신청서.docx", (req, res) => {
if (!isAxApplyDownloadSubmitAllowed(req, res)) {
return res
.status(403)
.type("text/plain; charset=utf-8")
.send("로그인 후 이용 가능합니다.");
}
const fp = path.join(RESOURCES_AX_APPLY_DIR, "AX_과제_신청서.docx");
if (!fsSync.existsSync(fp)) return res.status(404).send("Not found");
res.download(fp, "AX_과제_신청서.docx");
});
app.use("/resources/ax-apply", express.static(RESOURCES_AX_APPLY_DIR));
app.use(opsAuth.middleware);
opsAuth.registerRoutes(app);
const pageRouter = express.Router();
pageRouter.get("/chat", (req, res) =>
res.render("chat", {
activeMenu: "chat",
chatGptAllowed: isChatGptAllowed(req, res),
opsState: normalizeOpsState(),
adminMode: res.locals.adminMode,
opsUserEmail: !!res.locals.opsUserEmail,
})
);
pageRouter.get("/ai-explore", (req, res) =>
res.render("ai-explore", {
activeMenu: "ai-explore",
adminMode: res.locals.adminMode,
opsUserEmail: !!res.locals.opsUserEmail,
aiExploreDevGuestRestricted: isAiExploreDevGuestRestricted(req, res),
})
);
pageRouter.get("/ai-explore/prompts", (req, res) =>
res.render("ai-prompts", { activeMenu: "ai-explore", prompts: loadCompanyPrompts() })
);
pageRouter.get("/ai-explore/meeting-minutes", (req, res) =>
res.render("meeting-minutes", {
activeMenu: "ai-explore",
adminMode: res.locals.adminMode,
meetingUserEmail: getMeetingMinutesUserEmail(req, res) || "",
opsState: normalizeOpsState(),
})
);
pageRouter.get("/ai-explore/task-checklist", (req, res) =>
res.render("task-checklist", {
activeMenu: "ai-explore",
adminMode: res.locals.adminMode,
meetingUserEmail: getMeetingMinutesUserEmail(req, res) || "",
opsState: normalizeOpsState(),
})
);
const AI_SUCCESS_ADMIN_LIST_PAGE_SIZE = 5;
pageRouter.get("/ai-cases/write", (req, res) => {
if (!res.locals.adminMode) {
return res.status(403).send(
"권한 없음관리자 모드가 필요합니다. 좌측 하단 관리자에서 토큰을 입력한 뒤 다시 시도하세요.
AI 성공 사례 목록으로
"
);
}
const editSlug = (req.query.edit || "").trim();
const meta = loadAiSuccessStoriesMeta();
let story = null;
if (editSlug) {
const m = meta.find((x) => x.slug === editSlug);
if (m) story = enrichAiSuccessStory(m);
}
const sortedStories = [...meta].sort((a, b) => {
const da = new Date(a.publishedAt || a.updatedAt || a.createdAt || 0);
const db = new Date(b.publishedAt || b.updatedAt || b.createdAt || 0);
return db - da;
});
const pageRaw = req.query.page;
const pageNum = Math.max(1, parseInt(Array.isArray(pageRaw) ? pageRaw[0] : pageRaw, 10) || 1);
const totalCount = sortedStories.length;
const totalPages = Math.max(1, Math.ceil(totalCount / AI_SUCCESS_ADMIN_LIST_PAGE_SIZE));
const currentPage = Math.min(pageNum, totalPages);
const start = (currentPage - 1) * AI_SUCCESS_ADMIN_LIST_PAGE_SIZE;
const allStories = sortedStories.slice(start, start + AI_SUCCESS_ADMIN_LIST_PAGE_SIZE);
const listPagination = {
page: currentPage,
totalPages,
totalCount,
hasPrev: currentPage > 1,
hasNext: currentPage < totalPages,
};
res.render("ai-cases-write", {
activeMenu: "ai-cases",
adminMode: true,
story,
allStories,
listPagination,
editSlug: editSlug || null,
});
});
pageRouter.get("/ai-cases", async (req, res, next) => {
try {
const q = (req.query.q || "").trim();
const tag = (req.query.tag || "").trim();
const meta = loadAiSuccessStoriesMeta();
const filtered = filterAiSuccessStories(meta, q, tag);
const tags = allAiSuccessStoryTags(meta);
/** 목록에서도 카드 썸네일을 쓰기 위해 PDF→슬라이드가 없으면 생성 시도(상세와 동일 소스) */
await Promise.all(
filtered.slice(0, 24).map(async (m) => {
const pdfUrl = (m.pdfUrl || "").trim();
if (!pdfUrl) return;
if (getAiSuccessSlideImageUrls(m.slug).length > 0) return;
try {
await ensureAiSuccessStorySlides(m.slug, pdfUrl);
} catch (err) {
console.warn("[ai-cases] 슬라이드 워밍 실패:", m.slug, err?.message || err);
}
})
);
const stories = filtered.map((m) => {
const slideUrls = getAiSuccessSlideImageUrls(m.slug);
const coverImageUrl = slideUrls.length > 0 ? slideUrls[0] : "";
return { ...m, coverImageUrl };
});
res.render("ai-cases", {
activeMenu: "ai-cases",
adminMode: res.locals.adminMode,
opsUserEmail: !!res.locals.opsUserEmail,
successStoryDetailAllowed: isAiSuccessStoryDetailAllowed(req, res),
stories,
filters: { q, tag },
availableTags: tags,
});
} catch (err) {
next(err);
}
});
pageRouter.get("/ai-cases/:slug", async (req, res, next) => {
try {
if (!isAiSuccessStoryDetailAllowed(req, res)) {
return res.status(403).send(
"상세 열람 불가"
);
}
const slug = (req.params.slug || "").trim();
const meta = loadAiSuccessStoriesMeta();
const m = meta.find((x) => x.slug === slug);
if (!m) return res.status(404).send("사례를 찾을 수 없습니다.");
const story = enrichAiSuccessStory(m);
const pdfUrl = (story.pdfUrl || "").trim();
let slideImageUrls = [];
let slides = [];
if (pdfUrl) {
const out = await ensureAiSuccessStorySlides(slug, pdfUrl);
slideImageUrls = out.urls || [];
slides = slideImageUrls.map(() => ({ title: "", lines: [] }));
}
res.render("ai-case-detail", {
activeMenu: "ai-cases",
adminMode: res.locals.adminMode,
story,
slideImageUrls,
slides,
});
} catch (err) {
next(err);
}
});
pageRouter.get("/ax-apply", async (req, res) => {
let assignments = [];
if (res.locals.adminMode) {
assignments = await readAxAssignmentsDb();
} else {
const all = await readAxAssignmentsDb();
const sample = all.find(isSampleRecord);
if (sample) assignments = [sample];
}
res.render("ax-apply", {
activeMenu: "ax-apply",
assignments,
adminMode: res.locals.adminMode,
axApplyDownloadSubmitAllowed: isAxApplyDownloadSubmitAllowed(req, res),
});
});
app.get("/api/ax-apply-list", async (req, res) => {
const department = (req.query.department || "").trim();
const name = (req.query.name || "").trim();
const email = (req.query.email || "").trim();
if (res.locals.adminMode) {
const list = department && name && email
? await readAxAssignmentsByCredentials(department, name, email)
: await readAxAssignmentsDb();
return res.json(list);
}
if (!department || !name || !email) {
const allEmpty = await readAxAssignmentsDb();
const sample = allEmpty.find(isSampleRecord);
return res.json(sample ? [sample] : []);
}
const list = await readAxAssignmentsByCredentials(department, name, email);
const all = await readAxAssignmentsDb();
const sample = all.find(isSampleRecord);
if (sample && !list.some((m) => m.id === sample.id)) {
list.push(sample);
}
res.json(list);
});
app.get("/api/ax-apply/:id", async (req, res) => {
const id = (req.params.id || "").trim();
if (!id) return res.status(404).json({ error: "잘못된 요청입니다." });
const department = (req.query.department || "").trim();
const name = (req.query.name || "").trim();
const email = (req.query.email || "").trim();
const one = await readAxAssignmentById(id);
if (!one) return res.status(404).json({ error: "해당 신청을 찾을 수 없습니다." });
if (!res.locals.adminMode) {
if (isSampleRecord(one)) {
return res.json(one);
}
if (!department || !name || !email) return res.status(403).json({ error: "소속 부서, 이름, 이메일을 입력해 조회한 후 수정할 수 있습니다." });
const matches = await readAxAssignmentsByCredentials(department, name, email);
if (!matches.some((m) => m.id === id)) return res.status(403).json({ error: "본인의 신청만 수정할 수 있습니다." });
}
res.json(one);
});
app.put("/api/ax-apply/:id", (req, res, next) => {
uploadAxApply.single("applicationFile")(req, res, (err) => {
if (err) return res.status(400).json({ error: err.message || "파일 업로드 실패" });
next();
});
}, async (req, res) => {
try {
if (!isAxApplyDownloadSubmitAllowed(req, res)) {
return res.status(403).json({ error: "로그인 후 이용 가능합니다." });
}
const id = (req.params.id || "").trim();
if (!id) return res.status(404).json({ error: "잘못된 요청입니다." });
const body = { ...req.body };
const department = (body.department || "").trim();
const name = (body.name || "").trim();
const email = (body.email || "").trim();
if (!res.locals.adminMode) {
const existing = await readAxAssignmentById(id);
if (existing && isSampleRecord(existing)) return res.status(403).json({ error: "샘플 데이터는 참고용으로 수정할 수 없습니다." });
if (!department || !name || !email) return res.status(403).json({ error: "소속 부서, 이름, 이메일을 모두 입력해 주세요." });
const matches = await readAxAssignmentsByCredentials(department, name, email);
if (!matches.some((m) => m.id === id)) return res.status(403).json({ error: "본인의 신청만 수정할 수 있습니다." });
}
if (body.techStackStr && typeof body.techStackStr === "string") {
body.techStack = body.techStackStr.split(",").map((s) => s.trim()).filter(Boolean);
}
if (body.risksStr && typeof body.risksStr === "string") {
body.risks = body.risksStr.split(",").map((s) => s.trim()).filter(Boolean);
}
for (const k of ["dataTypes", "data_types", "techStackStr", "tech_stack", "risksStr", "qualitativeEffects", "qualitative_effects"]) {
if (body[k] === "" || (typeof body[k] === "string" && body[k] != null)) delete body[k];
}
if (body.techStack && !Array.isArray(body.techStack)) delete body.techStack;
if (body.risks && !Array.isArray(body.risks)) delete body.risks;
if (!email) return res.status(400).json({ error: "이메일을 입력해 주세요." });
if (req.file) body.applicationFile = `/uploads/ax-apply/${req.file.filename}`;
body.participationPledge = body.participationPledge === "1" || body.participationPledge === true;
const row = await updateAxAssignmentDb(id, body);
if (!row) return res.status(404).json({ error: "해당 신청을 찾을 수 없습니다." });
res.json({ ok: true, id: row.id });
} catch (err) {
const msg = err?.message || "저장 실패";
if (err instanceof ReferenceError && /updateAxAssignmentDb/.test(String(msg))) {
return res.status(500).json({ error: "서버를 재시작한 후 다시 시도해 주세요. (코드 반영이 필요할 수 있습니다.)" });
}
res.status(500).json({ error: msg });
}
});
app.delete("/api/ax-apply/:id", async (req, res) => {
if (!res.locals.adminMode) return res.status(403).json({ error: "관리자만 삭제할 수 있습니다." });
try {
const id = (req.params.id || "").trim();
if (!id) return res.status(404).json({ error: "잘못된 요청입니다." });
const ok = await deleteAxAssignmentDb(id);
if (!ok) return res.status(404).json({ error: "해당 신청을 찾을 수 없습니다." });
res.json({ ok: true });
} catch (err) {
res.status(500).json({ error: err?.message || "삭제 실패" });
}
});
app.post("/api/ax-apply", (req, res, next) => {
uploadAxApply.single("applicationFile")(req, res, (err) => {
if (err) return res.status(400).json({ error: err.message || "파일 업로드 실패" });
next();
});
}, async (req, res) => {
try {
if (!isAxApplyDownloadSubmitAllowed(req, res)) {
return res.status(403).json({ error: "로그인 후 이용 가능합니다." });
}
const body = { ...req.body };
if (body.techStackStr && typeof body.techStackStr === "string") {
body.techStack = body.techStackStr.split(",").map((s) => s.trim()).filter(Boolean);
}
if (body.risksStr && typeof body.risksStr === "string") {
body.risks = body.risksStr.split(",").map((s) => s.trim()).filter(Boolean);
}
for (const k of ["dataTypes", "data_types", "techStackStr", "tech_stack", "risksStr", "qualitativeEffects", "qualitative_effects"]) {
if (body[k] === "" || (typeof body[k] === "string" && body[k] != null)) delete body[k];
}
if (body.techStack && !Array.isArray(body.techStack)) delete body.techStack;
if (body.risks && !Array.isArray(body.risks)) delete body.risks;
if (!req.file) {
return res.status(400).json({ error: "작성 완료 신청서(.docx) 파일을 업로드해 주세요." });
}
const email = (body.email || "").trim();
if (!email) {
return res.status(400).json({ error: "이메일을 입력해 주세요." });
}
body.applicationFile = `/uploads/ax-apply/${req.file.filename}`;
body.participationPledge = body.participationPledge === "1" || body.participationPledge === true;
const row = await createAxAssignmentDb(body);
res.json({ ok: true, id: row.id });
} catch (err) {
const msg = err?.message || "저장 실패";
if (err instanceof ReferenceError && /updateAxAssignmentDb/.test(String(msg))) {
return res.status(500).json({ error: "서버를 재시작한 후 다시 시도해 주세요. (코드 반영이 필요할 수 있습니다.)" });
}
res.status(500).json({ error: msg });
}
});
const SYSTEM_PROMPT = [
"당신은 자비스(XAVIS) 플랫폼의 도우미입니다. 친절하고 정확하게 답변해주세요.",
"",
"웹 검색이나 제공된 검색 결과를 쓸 수 있으면 여러 출처를 종합해 답하세요.",
"회사·조직의 주소·위치·소재지·오시는 길 등을 묻는 질문에는 본사(또는 등기·공시상 대표 주소)뿐 아니라, 검색·자료에 나오면 공장·연구소·지사·지역 거점 등 주요 부속 시설을 구분해 짧게 정리해 포함하세요. 정보가 본사만 확인되는 경우에도 그 한계를 짧게 밝히고, 추가 거점이 공개되어 있으면 빠짐없이 반영하세요.",
].join("\n");
const getProvider = (model) => {
const m = (model || "").toString().toLowerCase();
if (m.startsWith("claude-")) return "anthropic";
if (m.startsWith("gemini-")) return "google";
return "openai";
};
const GEMINI_MODEL_MAP = {
"gemini-3-pro": "gemini-2.5-pro",
"gemini-3-flash": "gemini-2.5-flash",
};
/** 채팅 UI와 동일하게 허용하는 모델만 처리 */
const CHAT_ALLOWED_MODELS = new Set(["gpt-5.4", "gpt-5-mini"]);
/** UI 선택값 → OpenAI Chat Completions API에 넘길 실제 모델 ID (gpt-5.4 등은 API에 없을 수 있어 기본은 gpt-4o 계열) */
function resolveOpenAiApiModel(uiModel) {
const map = {
"gpt-5.4": (process.env.OPENAI_MODEL_DEFAULT || "gpt-4o").trim(),
"gpt-5-mini": (process.env.OPENAI_MODEL_MINI || "gpt-4o-mini").trim(),
};
return map[uiModel] || uiModel;
}
function buildResponsesInputFromChatMessages(normalizedMessages) {
const input = [];
for (const m of normalizedMessages) {
const r = String(m.role || "user").toLowerCase();
if (r !== "user" && r !== "assistant") continue;
input.push({
role: r === "assistant" ? "assistant" : "user",
content: m.content,
});
}
return input;
}
function buildOpenAiWebSearchTool() {
const tool = { type: "web_search" };
const country = (process.env.OPENAI_WEB_SEARCH_COUNTRY || "KR").trim();
if (country) {
tool.user_location = {
type: "approximate",
country,
};
const city = (process.env.OPENAI_WEB_SEARCH_CITY || "").trim();
const region = (process.env.OPENAI_WEB_SEARCH_REGION || "").trim();
const timezone = (process.env.OPENAI_WEB_SEARCH_TIMEZONE || "Asia/Seoul").trim();
if (city) tool.user_location.city = city;
if (region) tool.user_location.region = region;
if (timezone) tool.user_location.timezone = timezone;
}
return tool;
}
function extractSourcesFromResponse(response) {
const out = [];
const seen = new Set();
if (!response?.output) return out;
for (const item of response.output) {
if (item.type !== "message") continue;
for (const part of item.content || []) {
if (part.type !== "output_text" || !part.annotations) continue;
for (const ann of part.annotations) {
if (ann.type === "url_citation" && ann.url && !seen.has(ann.url)) {
seen.add(ann.url);
out.push({ url: ann.url, title: ann.title || ann.url });
}
}
}
}
return out;
}
/** 회의록 본문 수동 저장 — PATCH/PUT/POST(프록시·구버전 호환) */
async function saveMeetingGeneratedMinutesApi(req, res) {
try {
await ensureMeetingUserAndDefaultPrompt(req.meetingUserEmail);
const id = (req.params.id || "").trim();
const b = req.body || {};
const generatedMinutes = meetingMinutesLib.prepareMeetingMinutesForApi((b.generatedMinutes ?? "").toString());
if (!id) return res.status(400).json({ error: "회의 ID가 필요합니다." });
const prev = await meetingAiStore.getMeeting(pgPool, id, req.meetingUserEmail);
if (!prev) return res.status(404).json({ error: "회의록을 찾을 수 없습니다." });
const model = (prev.chat_model || "gpt-5-mini").toString().trim();
const fields = { generatedMinutes };
if (Object.prototype.hasOwnProperty.call(b, "transcriptText")) {
fields.transcriptText = (b.transcriptText ?? "").toString();
}
if (Object.prototype.hasOwnProperty.call(b, "sourceText")) {
fields.sourceText = (b.sourceText ?? "").toString();
}
const updated = await meetingAiStore.updateMeetingContent(pgPool, id, req.meetingUserEmail, fields);
if (!updated) return res.status(404).json({ error: "저장할 수 없습니다." });
let checklistSync = { imported: 0 };
if (OPENAI_API_KEY) {
try {
const openai = new OpenAI({ apiKey: OPENAI_API_KEY });
checklistSync = await syncAutoChecklistFromMeetingMinutes(openai, {
pgPool,
email: req.meetingUserEmail,
meetingId: id,
generatedMinutes,
uiModel: model,
});
} catch (e) {
checklistSync = { imported: 0, extractError: e?.message };
}
} else {
checklistSync = { imported: 0, disabled: true };
}
const fresh = await meetingAiStore.getMeeting(pgPool, id, req.meetingUserEmail);
const meetingOut = mapRowToMeeting(fresh);
if (checklistSync.snapshot) meetingOut.checklistSnapshot = checklistSync.snapshot;
res.json({ meeting: meetingOut, checklistSync });
} catch (err) {
res.status(500).json({ error: err?.message || "저장 실패" });
}
}
const meetingMinutesSaveJson = express.json({ limit: "2mb" });
app.get("/api/meeting-minutes/prompt", requireMeetingMinutesEmail, async (req, res) => {
try {
await ensureMeetingUserAndDefaultPrompt(req.meetingUserEmail);
const row = await meetingAiStore.getPromptRow(pgPool, req.meetingUserEmail);
res.json({ prompt: mapRowToMeetingPrompt(row) });
} catch (err) {
res.status(500).json({ error: err?.message || "조회 실패" });
}
});
app.put("/api/meeting-minutes/prompt", requireMeetingMinutesEmail, express.json(), async (req, res) => {
try {
await ensureMeetingUserAndDefaultPrompt(req.meetingUserEmail);
const b = req.body || {};
const includeTitleLine = b.includeTitleLine !== false;
const includeAttendees = b.includeAttendees !== false;
const includeSummary = b.includeSummary !== false;
const includeActionItems = b.includeActionItems !== false;
const includeChecklist = true;
const customInstructions = (b.customInstructions || "").toString().slice(0, 8000);
const row = await meetingAiStore.upsertPrompt(pgPool, req.meetingUserEmail, {
includeTitleLine,
includeAttendees,
includeSummary,
includeActionItems,
includeChecklist,
customInstructions,
});
res.json({ prompt: mapRowToMeetingPrompt(row) });
} catch (err) {
res.status(500).json({ error: err?.message || "저장 실패" });
}
});
app.get("/api/meeting-minutes/meetings", requireMeetingMinutesEmail, async (req, res) => {
try {
await ensureMeetingUserAndDefaultPrompt(req.meetingUserEmail);
const rows = await meetingAiStore.listMeetings(pgPool, req.meetingUserEmail);
res.json({ meetings: (rows || []).map(mapRowToMeeting) });
} catch (err) {
res.status(500).json({ error: err?.message || "목록 조회 실패" });
}
});
app.get("/api/meeting-minutes/meetings/:id", requireMeetingMinutesEmail, async (req, res) => {
try {
const id = (req.params.id || "").trim();
const row = await meetingAiStore.getMeeting(pgPool, id, req.meetingUserEmail);
if (!row) return res.status(404).json({ error: "회의록을 찾을 수 없습니다." });
res.json({ meeting: mapRowToMeeting(row) });
} catch (err) {
res.status(500).json({ error: err?.message || "조회 실패" });
}
});
app.patch("/api/meeting-minutes/meetings/:id", requireMeetingMinutesEmail, meetingMinutesSaveJson, saveMeetingGeneratedMinutesApi);
app.put("/api/meeting-minutes/meetings/:id", requireMeetingMinutesEmail, meetingMinutesSaveJson, saveMeetingGeneratedMinutesApi);
app.post("/api/meeting-minutes/meetings/:id/save", requireMeetingMinutesEmail, meetingMinutesSaveJson, saveMeetingGeneratedMinutesApi);
app.delete("/api/meeting-minutes/meetings/:id", requireMeetingMinutesEmail, async (req, res) => {
try {
const id = (req.params.id || "").trim();
const result = await meetingAiStore.deleteMeeting(pgPool, id, req.meetingUserEmail);
if (!result.deleted) return res.status(404).json({ error: "삭제할 항목이 없습니다." });
if (result.audio_file_path) {
const abs = path.join(ROOT_DIR, result.audio_file_path.replace(/^\//, ""));
const norm = path.normalize(abs);
if (norm.startsWith(MEETING_MINUTES_UPLOAD_DIR) && fsSync.existsSync(norm)) {
fsSync.unlinkSync(norm);
}
}
res.json({ ok: true });
} catch (err) {
res.status(500).json({ error: err?.message || "삭제 실패" });
}
});
app.post("/api/meeting-minutes/generate-text", requireMeetingMinutesEmail, express.json({ limit: "2mb" }), async (req, res) => {
if (!OPENAI_API_KEY) return res.status(503).json({ error: "OPENAI_API_KEY가 설정되지 않았습니다." });
try {
await ensureMeetingUserAndDefaultPrompt(req.meetingUserEmail);
const title = (req.body?.title || "").toString().trim().slice(0, 500);
const sourceText = (req.body?.sourceText || "").toString().trim();
const model = (req.body?.model || "gpt-5-mini").toString().trim();
const mdParsed = parseMeetingDateIso(req.body?.meetingDate);
if (mdParsed.error) return res.status(400).json({ error: mdParsed.error });
if (!sourceText) return res.status(400).json({ error: "회의 내용을 입력해 주세요." });
if (!MEETING_MINUTES_ALLOWED_MODELS.has(model)) {
return res.status(400).json({ error: "지원 모델: gpt-5-mini, gpt-5.4" });
}
const pr = await meetingAiStore.getPromptRow(pgPool, req.meetingUserEmail);
const systemPrompt = meetingMinutesLib.buildMeetingMinutesSystemPrompt(mapRowToMeetingPrompt(pr));
const openai = new OpenAI({ apiKey: OPENAI_API_KEY });
const generated = await meetingMinutesLib.generateMeetingMinutes(openai, {
systemPrompt,
userContent: sourceText,
uiModel: model,
resolveApiModel: resolveOpenAiApiModel,
});
const ins = await meetingAiStore.insertMeetingText(pgPool, {
email: req.meetingUserEmail,
title,
sourceText,
generated,
model,
meetingDate: mdParsed.value,
});
let checklistSync = { imported: 0 };
try {
checklistSync = await syncAutoChecklistFromMeetingMinutes(openai, {
pgPool,
email: req.meetingUserEmail,
meetingId: ins.id,
generatedMinutes: generated,
uiModel: model,
});
} catch (e) {
checklistSync = { imported: 0, extractError: e?.message };
}
const meetingOut = mapRowToMeeting(ins);
if (checklistSync.snapshot) meetingOut.checklistSnapshot = checklistSync.snapshot;
res.json({ meeting: meetingOut, checklistSync });
} catch (err) {
const msg = err?.response?.data?.error?.message || err?.message || "생성 실패";
res.status(500).json({ error: msg });
}
});
app.post(
"/api/meeting-minutes/generate-audio",
requireMeetingMinutesEmail,
(req, res, next) => {
uploadMeetingAudio.single("audio")(req, res, (err) => {
if (err) return res.status(400).json({ error: err.message || "파일 업로드 실패" });
next();
});
},
async (req, res) => {
if (!OPENAI_API_KEY) return res.status(503).json({ error: "OPENAI_API_KEY가 설정되지 않았습니다." });
try {
await ensureMeetingUserAndDefaultPrompt(req.meetingUserEmail);
const title = (req.body?.title || "").toString().trim().slice(0, 500);
const model = (req.body?.model || "gpt-5-mini").toString().trim();
const whisperModel = (req.body?.whisperModel || meetingMinutesLib.DEFAULT_TRANSCRIPTION_MODEL).toString().trim();
const mdParsed = parseMeetingDateIso(req.body?.meetingDate);
if (mdParsed.error) return res.status(400).json({ error: mdParsed.error });
if (!req.file) return res.status(400).json({ error: "음성 파일을 선택해 주세요." });
if (!MEETING_MINUTES_ALLOWED_MODELS.has(model)) {
return res.status(400).json({ error: "지원 모델: gpt-5-mini, gpt-5.4" });
}
if (!meetingMinutesLib.TRANSCRIPTION_UI_MODELS.has(whisperModel)) {
return res.status(400).json({ error: "지원 전사 모델: gpt-4o-mini-transcribe, gpt-4o-transcribe" });
}
const relPath = `/uploads/${path.relative(UPLOAD_DIR, req.file.path).split(path.sep).join("/")}`;
const pr = await meetingAiStore.getPromptRow(pgPool, req.meetingUserEmail);
const systemPrompt = meetingMinutesLib.buildMeetingMinutesSystemPrompt(mapRowToMeetingPrompt(pr));
const openai = new OpenAI({ apiKey: OPENAI_API_KEY });
let transcript = "";
try {
transcript = await meetingMinutesLib.transcribeMeetingAudio(openai, req.file.path, whisperModel);
} catch (te) {
const tmsg = te?.response?.data?.error?.message || te?.message || "전사 실패";
return res.status(500).json({ error: `Whisper 전사 실패: ${tmsg}` });
}
if (!transcript.trim()) return res.status(400).json({ error: "전사 결과가 비어 있습니다." });
const generated = await meetingMinutesLib.generateMeetingMinutes(openai, {
systemPrompt,
userContent: transcript,
uiModel: model,
resolveApiModel: resolveOpenAiApiModel,
});
const ins = await meetingAiStore.insertMeetingAudio(pgPool, {
email: req.meetingUserEmail,
title,
transcript,
generated,
relPath,
originalName: req.file.originalname,
model,
whisperModel,
meetingDate: mdParsed.value,
});
let checklistSync = { imported: 0 };
try {
checklistSync = await syncAutoChecklistFromMeetingMinutes(openai, {
pgPool,
email: req.meetingUserEmail,
meetingId: ins.id,
generatedMinutes: generated,
uiModel: model,
});
} catch (e) {
checklistSync = { imported: 0, extractError: e?.message };
}
const meetingOut = mapRowToMeeting(ins);
if (checklistSync.snapshot) meetingOut.checklistSnapshot = checklistSync.snapshot;
res.json({ meeting: meetingOut, checklistSync });
} catch (err) {
const msg = err?.response?.data?.error?.message || err?.message || "생성 실패";
res.status(500).json({ error: msg });
}
}
);
app.get("/api/task-checklist/items", requireMeetingMinutesEmail, async (req, res) => {
try {
await ensureMeetingUserAndDefaultPrompt(req.meetingUserEmail);
const completed = req.query.completed;
let cf = null;
if (completed === "true") cf = true;
else if (completed === "false") cf = false;
const midRaw = (req.query.meetingId || "").trim();
const meetingId = midRaw && midRaw !== "__all__" ? midRaw : null;
const items = await taskChecklistStore.listItems(pgPool, req.meetingUserEmail, {
completed: cf,
meetingId: meetingId || undefined,
});
const mids = items.map((it) => (it.meetingId != null ? String(it.meetingId) : "")).filter(Boolean);
const metaById = await meetingAiStore.getMeetingMetaForIds(pgPool, req.meetingUserEmail, mids);
const enriched = items.map((it) => {
const mid = it.meetingId != null ? String(it.meetingId) : "";
const meta = mid && metaById.has(mid) ? metaById.get(mid) : null;
return {
...it,
meetingTitle: meta ? meta.meetingTitle : null,
meetingDate: meta ? meta.meetingDate : null,
meetingSummary: meta ? meta.meetingSummary : null,
};
});
res.json({ items: enriched });
} catch (err) {
res.status(500).json({ error: err?.message || "목록 조회 실패" });
}
});
app.post("/api/task-checklist/items", requireMeetingMinutesEmail, express.json(), async (req, res) => {
try {
await ensureMeetingUserAndDefaultPrompt(req.meetingUserEmail);
const item = await taskChecklistStore.insertItem(pgPool, req.meetingUserEmail, {
title: req.body?.title,
detail: req.body?.detail,
assignee: req.body?.assignee,
dueNote: req.body?.dueNote,
meetingId: req.body?.meetingId || null,
source: "manual",
completed: !!req.body?.completed,
});
res.json({ item });
} catch (err) {
const msg = err?.message || "저장 실패";
res.status(400).json({ error: msg });
}
});
app.patch("/api/task-checklist/items/:id", requireMeetingMinutesEmail, express.json(), async (req, res) => {
try {
const id = (req.params.id || "").trim();
const item = await taskChecklistStore.updateItem(pgPool, id, req.meetingUserEmail, req.body || {});
if (!item) return res.status(404).json({ error: "항목을 찾을 수 없습니다." });
res.json({ item });
} catch (err) {
res.status(500).json({ error: err?.message || "수정 실패" });
}
});
app.delete("/api/task-checklist/items/:id", requireMeetingMinutesEmail, async (req, res) => {
try {
const id = (req.params.id || "").trim();
const result = await taskChecklistStore.deleteItem(pgPool, id, req.meetingUserEmail);
if (!result.deleted) return res.status(404).json({ error: "삭제할 항목이 없습니다." });
res.json({ ok: true });
} catch (err) {
res.status(500).json({ error: err?.message || "삭제 실패" });
}
});
app.post("/api/task-checklist/import/:meetingId", requireMeetingMinutesEmail, async (req, res) => {
try {
await ensureMeetingUserAndDefaultPrompt(req.meetingUserEmail);
const meetingId = (req.params.meetingId || "").trim();
const mode = (req.query.mode || "checklist").toString().trim() === "actions" ? "actions" : "checklist";
const row = await meetingAiStore.getMeeting(pgPool, meetingId, req.meetingUserEmail);
if (!row) return res.status(404).json({ error: "회의록을 찾을 수 없습니다." });
const text = row.generated_minutes || "";
if (!text.trim()) return res.status(400).json({ error: "회의록 본문이 비어 있습니다." });
const parsed = parseChecklistFromMinutes.parseItemsFromMinutes(text, mode);
if (!parsed.length) {
return res.status(400).json({
error:
mode === "actions"
? "‘액션 아이템’ 섹션에서 항목을 찾지 못했습니다. 제목 형식(번호 목록)을 확인해 주세요."
: "‘후속 확인 체크리스트’ 등 체크리스트 섹션에서 항목을 찾지 못했습니다.",
parsedCount: 0,
});
}
const inserted = await taskChecklistStore.insertImportedBatch(pgPool, req.meetingUserEmail, meetingId, parsed);
res.json({ imported: inserted.length, items: inserted });
} catch (err) {
res.status(500).json({ error: err?.message || "가져오기 실패" });
}
});
/** 모든 회의록에 대해 규칙 기반 가져오기(회의별 중복은 기존과 동일하게 스킵) */
app.post("/api/task-checklist/import-all", requireMeetingMinutesEmail, async (req, res) => {
try {
await ensureMeetingUserAndDefaultPrompt(req.meetingUserEmail);
const mode = (req.query.mode || "checklist").toString().trim() === "actions" ? "actions" : "checklist";
const rows = await meetingAiStore.listMeetings(pgPool, req.meetingUserEmail);
if (!rows.length) {
return res.status(400).json({ error: "가져올 회의록이 없습니다." });
}
let imported = 0;
/** @type {Array<{ meetingId: string, title: string, imported: number, skip?: string }>} */
const details = [];
for (const row of rows) {
const meetingId = row.id != null ? String(row.id) : "";
const title = (row.title && String(row.title).trim()) || "제목 없음";
if (!meetingId) continue;
const text = row.generated_minutes || "";
if (!String(text).trim()) {
details.push({ meetingId, title, imported: 0, skip: "empty_body" });
continue;
}
const parsed = parseChecklistFromMinutes.parseItemsFromMinutes(text, mode);
if (!parsed.length) {
details.push({ meetingId, title, imported: 0, skip: "no_parsed_items" });
continue;
}
const inserted = await taskChecklistStore.insertImportedBatch(pgPool, req.meetingUserEmail, meetingId, parsed);
const n = inserted.length;
imported += n;
details.push({ meetingId, title, imported: n });
}
const meetingsWithImports = details.filter((d) => d.imported > 0).length;
res.json({
imported,
meetingCount: rows.length,
meetingsWithImports,
details,
});
} catch (err) {
res.status(500).json({ error: err?.message || "가져오기 실패" });
}
});
app.get("/api/chat/config", (req, res) => {
res.json({
configured: OPENAI_API_KEY.length > 0,
webSearch: OPENAI_WEB_SEARCH,
chatGptAllowed: isChatGptAllowed(req, res),
});
});
app.post("/api/chat", async (req, res) => {
if (!isChatGptAllowed(req, res)) {
res.status(403).json({ error: "허용된 사용자가 아닙니다." });
return;
}
const { messages, model } = req.body;
if (!Array.isArray(messages) || messages.length === 0) {
res.status(400).json({ error: "messages 배열이 필요합니다." });
return;
}
const chatModel = (model || "gpt-5-mini").toString().trim() || "gpt-5-mini";
if (!CHAT_ALLOWED_MODELS.has(chatModel)) {
res.status(400).json({ error: "지원하지 않는 모델입니다. gpt-5.4 또는 gpt-5-mini만 사용할 수 있습니다." });
return;
}
const provider = getProvider(chatModel);
const normalizedMessages = messages.map((m) => ({
role: m.role || "user",
content: String(m.content || ""),
}));
try {
let reply = "";
let sources;
if (provider === "openai") {
if (!OPENAI_API_KEY) {
res.status(503).json({ error: "OPENAI_API_KEY가 설정되지 않았습니다. .env 파일을 확인한 뒤 서버를 재시작해주세요." });
return;
}
const apiModel = resolveOpenAiApiModel(chatModel);
const openai = new OpenAI({ apiKey: OPENAI_API_KEY });
if (OPENAI_WEB_SEARCH) {
const resp = await openai.responses.create({
model: apiModel,
instructions: SYSTEM_PROMPT,
input: buildResponsesInputFromChatMessages(normalizedMessages),
tools: [buildOpenAiWebSearchTool()],
include: ["web_search_call.action.sources"],
});
reply = resp.output_text || "";
sources = extractSourcesFromResponse(resp);
} else {
const completion = await openai.chat.completions.create({
model: apiModel,
messages: [
{ role: "system", content: SYSTEM_PROMPT },
...normalizedMessages,
],
});
reply = completion.choices?.[0]?.message?.content || "";
}
} else if (provider === "anthropic") {
if (!CLAUDE_API_KEY) {
res.status(503).json({ error: "CLAUDE_API_KEY가 설정되지 않았습니다. .env 파일을 확인해주세요." });
return;
}
const anthropic = new Anthropic({ apiKey: CLAUDE_API_KEY });
const claudeMessages = normalizedMessages
.filter((m) => m.role === "user" || m.role === "assistant")
.map((m) => ({ role: m.role, content: m.content }));
const msg = await anthropic.messages.create({
model: chatModel,
max_tokens: 4096,
system: SYSTEM_PROMPT,
messages: claudeMessages,
});
const textBlock = msg.content?.find((b) => b.type === "text");
reply = textBlock?.text || "";
} else if (provider === "google") {
if (!GENAI_API_KEY) {
res.status(503).json({ error: "GENAI_API_KEY가 설정되지 않았습니다. .env 파일을 확인해주세요." });
return;
}
const geminiModel = GEMINI_MODEL_MAP[chatModel] || chatModel;
const ai = new GoogleGenAI({ apiKey: GENAI_API_KEY });
const geminiContents = normalizedMessages.map((m) => ({
role: m.role === "assistant" ? "model" : "user",
parts: [{ text: m.content }],
}));
const response = await ai.models.generateContent({
model: geminiModel,
contents: geminiContents,
config: { systemInstruction: SYSTEM_PROMPT },
});
reply = response?.text || "";
}
const payload = { message: reply };
if (sources && sources.length) payload.sources = sources;
res.json(payload);
} catch (err) {
const apiMsg =
err?.error?.message ||
err?.response?.data?.error?.message ||
err?.message ||
`${provider} API 오류`;
const status =
typeof err?.status === "number"
? err.status
: err?.response?.status && err.response.status >= 400 && err.response.status < 600
? err.response.status
: 500;
res.status(status >= 400 && status < 600 ? status : 500).json({ error: apiMsg });
}
});
function writeChatSse(res, obj) {
res.write(`data: ${JSON.stringify(obj)}\n\n`);
}
/** OpenAI Chat Completions 스트리밍 (SSE). 클라이언트는 fetch + ReadableStream으로 수신 */
app.post("/api/chat/stream", async (req, res) => {
const { messages, model } = req.body;
if (!Array.isArray(messages) || messages.length === 0) {
res.status(400).json({ error: "messages 배열이 필요합니다." });
return;
}
if (!isChatGptAllowed(req, res)) {
res.status(403).json({ error: "허용된 사용자가 아닙니다." });
return;
}
const chatModel = (model || "gpt-5-mini").toString().trim() || "gpt-5-mini";
if (!CHAT_ALLOWED_MODELS.has(chatModel)) {
res.status(400).json({ error: "지원하지 않는 모델입니다. gpt-5.4 또는 gpt-5-mini만 사용할 수 있습니다." });
return;
}
const provider = getProvider(chatModel);
if (provider !== "openai") {
res.status(400).json({ error: "스트리밍은 OpenAI 모델만 지원합니다." });
return;
}
if (!OPENAI_API_KEY) {
res.status(503).json({ error: "OPENAI_API_KEY가 설정되지 않았습니다. .env 파일을 확인한 뒤 서버를 재시작해주세요." });
return;
}
const normalizedMessages = messages.map((m) => ({
role: m.role || "user",
content: String(m.content || ""),
}));
res.setHeader("Content-Type", "text/event-stream; charset=utf-8");
res.setHeader("Cache-Control", "no-cache, no-transform");
res.setHeader("Connection", "keep-alive");
res.setHeader("X-Accel-Buffering", "no");
const apiModel = resolveOpenAiApiModel(chatModel);
const openai = new OpenAI({ apiKey: OPENAI_API_KEY });
try {
if (OPENAI_WEB_SEARCH) {
const stream = await openai.responses.create({
model: apiModel,
instructions: SYSTEM_PROMPT,
input: buildResponsesInputFromChatMessages(normalizedMessages),
tools: [buildOpenAiWebSearchTool()],
stream: true,
include: ["web_search_call.action.sources"],
});
for await (const event of stream) {
if (event.type === "response.output_text.delta") {
const d = event.delta;
if (d) writeChatSse(res, { type: "delta", text: d });
} else if (
event.type === "response.web_search_call.searching" ||
event.type === "response.web_search_call.in_progress"
) {
writeChatSse(res, { type: "status", phase: "web_search" });
} else if (event.type === "response.completed") {
const items = extractSourcesFromResponse(event.response);
if (items.length) writeChatSse(res, { type: "sources", items });
} else if (event.type === "error") {
throw new Error(event.message || "Responses API 오류");
} else if (event.type === "response.failed") {
const msg = event.response?.error?.message || "응답 생성 실패";
throw new Error(msg);
}
}
writeChatSse(res, { type: "done" });
res.end();
return;
}
const stream = await openai.chat.completions.create({
model: apiModel,
messages: [{ role: "system", content: SYSTEM_PROMPT }, ...normalizedMessages],
stream: true,
});
for await (const chunk of stream) {
const delta = chunk.choices?.[0]?.delta?.content ?? "";
if (delta) writeChatSse(res, { type: "delta", text: delta });
}
writeChatSse(res, { type: "done" });
res.end();
} catch (err) {
const apiMsg =
err?.error?.message ||
err?.response?.data?.error?.message ||
err?.message ||
"OpenAI API 오류";
try {
writeChatSse(res, { type: "error", error: apiMsg });
res.end();
} catch {
if (!res.headersSent) {
res.status(500).json({ error: apiMsg });
}
}
}
});
app.post(
"/api/ai-success-stories/upload-pdf",
requireAdminApi,
(req, res, next) => {
uploadAiSuccessPdf.single("pdfFile")(req, res, (err) => {
if (err) {
res.status(400).json({ error: err.message || "업로드 실패" });
return;
}
next();
});
},
(req, res) => {
try {
if (!req.file) {
res.status(400).json({ error: "PDF 파일을 선택해 주세요." });
return;
}
const rel = path.relative(path.join(ROOT_DIR, "public"), req.file.path).replace(/\\/g, "/");
const pdfUrl = `/public/${rel}`;
res.json({ ok: true, pdfUrl, filename: req.file.filename });
} catch (err) {
res.status(500).json({ error: err?.message || "저장 실패" });
}
}
);
app.post("/api/ai-success-stories", requireAdminApi, (req, res) => {
try {
const body = req.body || {};
const title = String(body.title || "").trim();
let slug = String(body.slug || "")
.trim()
.toLowerCase()
.replace(/[^a-z0-9\-]/g, "");
const bodyMd = String(body.bodyMarkdown || "").trim();
const pdfUrlTrim = String(body.pdfUrl || "").trim();
if (!title || !slug) {
res.status(400).json({ error: "제목과 슬러그(영문·숫자·하이픈)는 필수입니다." });
return;
}
if (!bodyMd && !pdfUrlTrim) {
res.status(400).json({ error: "본문(Markdown) 또는 원문 PDF 중 하나는 필수입니다." });
return;
}
const meta = loadAiSuccessStoriesMeta();
if (meta.some((m) => m.slug === slug)) {
res.status(400).json({ error: "같은 슬러그가 이미 있습니다." });
return;
}
const id = `story-${Date.now()}`;
const tags = String(body.tags || "")
.split(",")
.map((t) => t.trim())
.filter(Boolean);
const fileName = buildAiSuccessStoryContentFileName(slug);
fsSync.mkdirSync(AI_SUCCESS_CONTENT_DIR, { recursive: true });
fsSync.writeFileSync(path.join(AI_SUCCESS_CONTENT_DIR, fileName), bodyMd, "utf8");
const row = {
id,
slug,
title,
excerpt: String(body.excerpt || "").trim() || title.slice(0, 140),
author: String(body.author || "").trim(),
department: String(body.department || "").trim(),
publishedAt: String(body.publishedAt || "").trim() || new Date().toISOString().slice(0, 10),
tags,
contentFile: fileName,
pdfUrl: String(body.pdfUrl || "").trim(),
createdAt: new Date().toISOString(),
updatedAt: new Date().toISOString(),
};
meta.push(row);
saveAiSuccessStoriesMeta(meta);
res.json({ ok: true, story: row });
} catch (err) {
res.status(500).json({ error: err?.message || "저장 실패" });
}
});
app.put("/api/ai-success-stories/:id", requireAdminApi, (req, res) => {
try {
const id = req.params.id;
const meta = loadAiSuccessStoriesMeta();
const idx = meta.findIndex((m) => m.id === id);
if (idx === -1) {
res.status(404).json({ error: "해당 사례가 없습니다." });
return;
}
const body = req.body || {};
const cur = meta[idx];
const title = body.title !== undefined ? String(body.title).trim() : cur.title;
let slug = body.slug !== undefined
? String(body.slug)
.trim()
.toLowerCase()
.replace(/[^a-z0-9\-]/g, "")
: cur.slug;
const bodyMd =
body.bodyMarkdown !== undefined ? String(body.bodyMarkdown) : loadStoryBodyMarkdown(cur);
const nextPdf = body.pdfUrl !== undefined ? String(body.pdfUrl).trim() : (cur.pdfUrl || "").trim();
if (!title || !slug) {
res.status(400).json({ error: "제목과 슬러그는 필수입니다." });
return;
}
if (!bodyMd.trim() && !nextPdf) {
res.status(400).json({ error: "본문(Markdown) 또는 원문 PDF 중 하나는 필수입니다." });
return;
}
if (meta.some((m, i) => m.slug === slug && i !== idx)) {
res.status(400).json({ error: "같은 슬러그가 이미 있습니다." });
return;
}
const oldSlug = cur.slug;
const oldFileBase = cur.contentFile ? path.basename(cur.contentFile) : `${oldSlug}.md`;
let nextFile;
if (slug !== oldSlug) {
nextFile = buildAiSuccessStoryContentFileName(slug);
} else {
nextFile = cur.contentFile ? path.basename(cur.contentFile) : buildAiSuccessStoryContentFileName(slug);
}
const safeFile = path.basename(nextFile);
fsSync.mkdirSync(AI_SUCCESS_CONTENT_DIR, { recursive: true });
fsSync.writeFileSync(path.join(AI_SUCCESS_CONTENT_DIR, safeFile), bodyMd, "utf8");
if (oldFileBase !== safeFile) {
try {
fsSync.unlinkSync(path.join(AI_SUCCESS_CONTENT_DIR, oldFileBase));
} catch {
/* ignore */
}
}
meta[idx] = {
...cur,
title,
slug,
excerpt: body.excerpt !== undefined ? String(body.excerpt).trim() : cur.excerpt,
author: body.author !== undefined ? String(body.author).trim() : cur.author,
department: body.department !== undefined ? String(body.department).trim() : cur.department,
publishedAt: body.publishedAt !== undefined ? String(body.publishedAt).trim() : cur.publishedAt,
tags:
body.tags !== undefined
? String(body.tags)
.split(",")
.map((t) => t.trim())
.filter(Boolean)
: cur.tags,
contentFile: safeFile,
pdfUrl: body.pdfUrl !== undefined ? String(body.pdfUrl).trim() : cur.pdfUrl || "",
updatedAt: new Date().toISOString(),
};
saveAiSuccessStoriesMeta(meta);
res.json({ ok: true, story: meta[idx] });
} catch (err) {
res.status(500).json({ error: err?.message || "저장 실패" });
}
});
app.delete("/api/ai-success-stories/:id", requireAdminApi, (req, res) => {
try {
const id = req.params.id;
const meta = loadAiSuccessStoriesMeta();
const idx = meta.findIndex((m) => m.id === id);
if (idx === -1) {
res.status(404).json({ error: "해당 사례가 없습니다." });
return;
}
const cur = meta[idx];
const cf = cur.contentFile;
if (cf) {
try {
fsSync.unlinkSync(path.join(AI_SUCCESS_CONTENT_DIR, path.basename(cf)));
} catch {
/* ignore */
}
}
meta.splice(idx, 1);
saveAiSuccessStoriesMeta(meta);
res.json({ ok: true });
} catch (err) {
res.status(500).json({ error: err?.message || "삭제 실패" });
}
});
app.use("/", pageRouter);
const decodeXmlText = (value) =>
value
.replace(/&/g, "&")
.replace(/</g, "<")
.replace(/>/g, ">")
.replace(/"/g, '"')
.replace(/'/g, "'");
const escapeHtml = (value) =>
(value || "")
.replace(/&/g, "&")
.replace(//g, ">")
.replace(/"/g, """)
.replace(/'/g, "'");
const isYoutubeUrl = (url) =>
/^(https?:\/\/)?(www\.)?(youtube\.com|youtu\.be)\//i.test((url || "").trim());
/** http(s) 외부 링크 (뉴스 URL 등) */
const isHttpUrl = (url) => {
const s = (url || "").trim();
if (!s) return false;
try {
const u = new URL(s);
return u.protocol === "http:" || u.protocol === "https:";
} catch {
return false;
}
};
const normalizeLecture = (lecture) => {
if (!lecture) return lecture;
if (!lecture.listSection) lecture.listSection = "learning";
return lecture;
};
const toYoutubeEmbedUrl = (url) => {
const value = (url || "").trim();
try {
const parsed = new URL(value);
if (parsed.hostname.includes("youtu.be")) {
const videoId = parsed.pathname.replace("/", "");
return `https://www.youtube.com/embed/${videoId}`;
}
if (parsed.hostname.includes("youtube.com")) {
const videoId = parsed.searchParams.get("v");
if (videoId) return `https://www.youtube.com/embed/${videoId}`;
if (parsed.pathname.startsWith("/embed/")) return value;
}
return value;
} catch {
return value;
}
};
const getYoutubeThumbnailUrl = (url) => {
if (!url || !isYoutubeUrl(url)) return null;
try {
const parsed = new URL((url || "").trim());
let videoId = null;
if (parsed.hostname.includes("youtu.be")) {
videoId = parsed.pathname.replace(/^\/+/, "").split("/")[0];
} else if (parsed.hostname.includes("youtube.com")) {
videoId = parsed.searchParams.get("v");
if (!videoId && parsed.pathname.startsWith("/embed/")) {
videoId = (parsed.pathname.match(/\/embed\/([^/?]+)/) || [])[1];
}
}
if (videoId) return `https://img.youtube.com/vi/${videoId}/hqdefault.jpg`;
} catch {}
return null;
};
app.locals.getYoutubeThumbnailUrl = getYoutubeThumbnailUrl;
const mapRowToLecture = (row) =>
normalizeLecture({
id: row.id,
type: row.type,
title: row.title,
description: row.description || "",
tags: Array.isArray(row.tags) ? row.tags : [],
youtubeUrl: row.youtube_url || null,
fileName: row.file_name || null,
originalName: row.original_name || null,
previewTitle: row.preview_title || null,
slideCount: typeof row.slide_count === "number" ? row.slide_count : 0,
thumbnailUrl: row.thumbnail_url || null,
thumbnailStatus: row.thumbnail_status || "pending",
thumbnailRetryCount: typeof row.thumbnail_retry_count === "number" ? row.thumbnail_retry_count : 0,
thumbnailError: row.thumbnail_error || null,
thumbnailUpdatedAt: row.thumbnail_updated_at ? new Date(row.thumbnail_updated_at).toISOString() : null,
createdAt: row.created_at ? new Date(row.created_at).toISOString() : new Date().toISOString(),
listSection: row.list_section || "learning",
newsUrl: row.news_url || null,
});
const readLectureDb = async () => {
if (pgPool) {
try {
const res = await pgPool.query(
"SELECT id, type, title, description, tags, youtube_url, file_name, original_name, preview_title, slide_count, thumbnail_url, thumbnail_status, thumbnail_retry_count, thumbnail_error, thumbnail_updated_at, created_at, list_section, news_url FROM lectures ORDER BY created_at DESC"
);
return (res.rows || []).map(mapRowToLecture);
} catch (err) {
console.error("readLectureDb from PostgreSQL failed:", err.message);
throw err;
}
}
try {
const raw = await fs.readFile(LECTURE_DB_PATH, "utf-8");
const parsed = JSON.parse(raw);
const list = Array.isArray(parsed) ? parsed : [];
return list.map((item) => normalizeLecture({ ...item }));
} catch {
return [];
}
};
const writeLectureDb = async (lectures) => {
if (pgPool) {
await syncLecturesToPostgres(lectures);
return;
}
await fs.writeFile(LECTURE_DB_PATH, JSON.stringify(lectures, null, 2), "utf-8");
};
/** link·news: og:image URL을 thumbnailUrl(절대 URL)에 저장 */
const applyLinkOgPreviewToLecture = async (lectureId, pageUrl) => {
const img = await fetchOpenGraphImageUrl(pageUrl);
const lectures = await readLectureDb();
const lec = lectures.find((l) => l.id === lectureId);
if (!lec || (lec.type !== "link" && lec.type !== "news")) return;
lec.thumbnailUrl = img || null;
lec.thumbnailStatus = img ? "ready" : "failed";
lec.thumbnailError = img ? null : "og:image 없음";
lec.thumbnailUpdatedAt = new Date().toISOString();
if (typeof lec.thumbnailRetryCount !== "number") lec.thumbnailRetryCount = 0;
await writeLectureDb(lectures);
};
const scheduleLinkOgPreviewFetch = (lectureId, pageUrl) => {
setImmediate(() => {
applyLinkOgPreviewToLecture(lectureId, pageUrl).catch((e) =>
console.error("[link-preview]", lectureId, e?.message || e)
);
});
};
/** 목록에 표시 중인 pending 링크에 대해 서버당 1회 OG fetch 예약(기존 등록 건 보정) */
const linkOgPreviewScheduledIds = new Set();
const parseTags = (value) =>
(value || "")
.split(",")
.map((tag) => tag.trim())
.filter(Boolean)
.filter((tag, idx, arr) => arr.indexOf(tag) === idx);
const LECTURE_CATEGORIES = ["AX 사고 전환", "AI 툴 활용", "AI Agent", "바이브 코딩"];
const mergeTagsWithCategory = (tagsStr, category) => {
const tags = parseTags(tagsStr);
const cat = (category || "").toString().trim();
if (cat && LECTURE_CATEGORIES.includes(cat) && !tags.includes(cat)) {
tags.push(cat);
}
return tags;
};
const parsePptxSlides = async (filePath) => {
const buffer = await fs.readFile(filePath);
const zip = await JSZip.loadAsync(buffer);
const slideFiles = Object.keys(zip.files)
.filter((name) => /^ppt\/slides\/slide\d+\.xml$/.test(name))
.sort((a, b) => {
const aNum = Number((a.match(/slide(\d+)\.xml/) || [])[1] || 0);
const bNum = Number((b.match(/slide(\d+)\.xml/) || [])[1] || 0);
return aNum - bNum;
});
const slides = [];
for (const slideFile of slideFiles) {
const xml = await zip.files[slideFile].async("string");
const texts = [];
const matcher = xml.matchAll(/]*>([\s\S]*?)<\/a:t>/g);
for (const matched of matcher) {
const plain = decodeXmlText(matched[1] || "").trim();
if (plain) texts.push(plain);
}
slides.push({
title: texts[0] || "",
lines: texts,
});
}
return slides;
};
const getPptxMeta = async (filePath) => {
const slides = await parsePptxSlides(filePath);
const first = slides[0] || { title: "" };
return {
slideCount: slides.length,
previewTitle: first.title || "",
};
};
const getPdfMeta = async (filePath) => {
try {
const { stdout } = await execFileAsync("pdfinfo", [filePath], { timeout: 10000 });
const match = (stdout || "").match(/Pages:\s*(\d+)/);
const slideCount = match ? Math.max(1, parseInt(match[1], 10)) : 0;
return { slideCount, previewTitle: "" };
} catch {
return { slideCount: 0, previewTitle: "" };
}
};
const parsePdfSlides = async (filePath) => {
const meta = await getPdfMeta(filePath);
return Array.from({ length: meta.slideCount }, (_, i) => ({
title: "",
lines: [],
}));
};
const tryExec = async (command, args, timeout = 20000) => {
try {
await execFileAsync(command, args, {
timeout,
windowsHide: true,
});
return true;
} catch (err) {
return false;
}
};
const tryExecWithLog = async (command, args, timeout, logContext) => {
try {
await execFileAsync(command, args, {
timeout,
windowsHide: true,
});
return { ok: true };
} catch (err) {
console.error(`[slide-images] ${logContext}: ${command} failed:`, err.message || err);
return { ok: false, error: err.message };
}
};
const safeRemove = async (targetPath) => {
try {
await fs.rm(targetPath, { recursive: true, force: true });
} catch {
// Ignore cleanup errors.
}
};
const generateWithQuickLook = async (filePath, targetKey) => {
if (process.platform !== "darwin") return null;
const qlmanagePath = "/usr/bin/qlmanage";
if (!fsSync.existsSync(qlmanagePath)) return null;
const before = new Set(await fs.readdir(THUMBNAIL_DIR));
const ok = await tryExec(qlmanagePath, ["-t", "-s", String(THUMBNAIL_WIDTH), "-o", THUMBNAIL_DIR, filePath]);
if (!ok) return null;
const after = await fs.readdir(THUMBNAIL_DIR);
const createdCandidates = after.filter((name) => !before.has(name) && /\.(png|jpg|jpeg)$/i.test(name));
const base = path.basename(filePath);
const matched = createdCandidates.find((name) => name.includes(base)) || createdCandidates[0];
if (!matched) return null;
const sourcePath = path.join(THUMBNAIL_DIR, matched);
const ext = path.extname(matched) || ".png";
const finalName = `${targetKey}${ext.toLowerCase()}`;
const finalPath = path.join(THUMBNAIL_DIR, finalName);
try {
await fs.rename(sourcePath, finalPath);
} catch {
return null;
}
return `/uploads/thumbnails/${finalName}`;
};
const generateWithLibreOffice = async (filePath, targetKey) => {
const tmpWorkDir = path.join(TMP_DIR, `thumb-${targetKey}-${Date.now()}`);
await fs.mkdir(tmpWorkDir, { recursive: true });
const sofficeCandidates = ["soffice", "libreoffice"];
let converted = false;
for (const cmd of sofficeCandidates) {
// soffice --headless --convert-to pdf --outdir
const ok = await tryExec(cmd, ["--headless", "--convert-to", "pdf", "--outdir", tmpWorkDir, filePath], 60000);
if (ok) {
converted = true;
break;
}
}
if (!converted) {
await safeRemove(tmpWorkDir);
return null;
}
const pdfPath = path.join(tmpWorkDir, `${path.basename(filePath, path.extname(filePath))}.pdf`);
if (!fsSync.existsSync(pdfPath)) {
await safeRemove(tmpWorkDir);
return null;
}
const outPrefix = path.join(tmpWorkDir, "preview");
const okPpm = await tryExec("pdftoppm", ["-f", "1", "-singlefile", "-png", pdfPath, outPrefix], 30000);
if (!okPpm) {
await safeRemove(tmpWorkDir);
return null;
}
const generatedPng = `${outPrefix}.png`;
if (!fsSync.existsSync(generatedPng)) {
await safeRemove(tmpWorkDir);
return null;
}
const finalName = `${targetKey}.png`;
const finalPath = path.join(THUMBNAIL_DIR, finalName);
try {
await fs.copyFile(generatedPng, finalPath);
} catch {
await safeRemove(tmpWorkDir);
return null;
}
await safeRemove(tmpWorkDir);
return `/uploads/thumbnails/${finalName}`;
};
const generateWithPdfDirect = async (filePath, targetKey) => {
const ext = path.extname(filePath).toLowerCase();
if (ext !== ".pdf") return null;
const tmpWorkDir = path.join(TMP_DIR, `thumb-pdf-${targetKey}-${Date.now()}`);
await fs.mkdir(tmpWorkDir, { recursive: true });
const outPrefix = path.join(tmpWorkDir, "preview");
const okPpm = await tryExec("pdftoppm", ["-f", "1", "-singlefile", "-png", "-r", "150", filePath, outPrefix], 30000);
if (!okPpm) {
await safeRemove(tmpWorkDir);
return null;
}
const generatedPng = `${outPrefix}.png`;
if (!fsSync.existsSync(generatedPng)) {
await safeRemove(tmpWorkDir);
return null;
}
const finalName = `${targetKey}.png`;
const finalPath = path.join(THUMBNAIL_DIR, finalName);
try {
await fs.copyFile(generatedPng, finalPath);
} catch {
await safeRemove(tmpWorkDir);
return null;
}
await safeRemove(tmpWorkDir);
return `/uploads/thumbnails/${finalName}`;
};
const generatePptThumbnail = async (filePath, targetKey) => {
if (!ENABLE_PPT_THUMBNAIL) return null;
await fs.mkdir(THUMBNAIL_DIR, { recursive: true });
const pdfDirect = await generateWithPdfDirect(filePath, targetKey);
if (pdfDirect) return pdfDirect;
const quickLook = await generateWithQuickLook(filePath, targetKey);
if (quickLook) return quickLook;
const libre = await generateWithLibreOffice(filePath, targetKey);
if (libre) return libre;
return null;
};
const generateSlideImages = async (filePath, lectureId) => {
const outDir = path.join(SLIDES_DIR, lectureId);
await fs.mkdir(outDir, { recursive: true });
const ext = path.extname(filePath).toLowerCase();
const tmpWorkDir = path.join(TMP_DIR, `slides-${lectureId}-${Date.now()}`);
await fs.mkdir(tmpWorkDir, { recursive: true });
let pdfPath = filePath;
if (ext === ".pptx") {
const libreOfficeCandidates = [
"soffice",
"libreoffice",
...(process.platform === "darwin"
? ["/Applications/LibreOffice.app/Contents/MacOS/soffice"]
: []),
];
let converted = false;
for (const cmd of libreOfficeCandidates) {
const { ok } = await tryExecWithLog(
cmd,
["--headless", "--convert-to", "pdf", "--outdir", tmpWorkDir, filePath],
90000,
`lecture ${lectureId} PPTX→PDF`
);
if (ok) {
converted = true;
break;
}
}
if (!converted) {
console.error(`[slide-images] lecture ${lectureId}: PPTX 변환 실패. LibreOffice(soffice/libreoffice) 설치 필요. brew install --cask libreoffice`);
await safeRemove(tmpWorkDir);
return null;
}
pdfPath = path.join(tmpWorkDir, `${path.basename(filePath, path.extname(filePath))}.pdf`);
if (!fsSync.existsSync(pdfPath)) {
console.error(`[slide-images] lecture ${lectureId}: 변환된 PDF 파일을 찾을 수 없음: ${pdfPath}`);
await safeRemove(tmpWorkDir);
return null;
}
} else if (ext !== ".pdf") {
await safeRemove(tmpWorkDir);
return null;
}
const outPrefix = path.join(tmpWorkDir, "slide");
const { ok: okPpm } = await tryExecWithLog(
"pdftoppm",
["-png", "-r", "150", pdfPath, outPrefix],
60000,
`lecture ${lectureId} pdftoppm`
);
if (!okPpm) {
await safeRemove(tmpWorkDir);
return null;
}
const slidePngs = fsSync.readdirSync(tmpWorkDir)
.filter((n) => /^slide-\d+\.png$/.test(n))
.sort((a, b) => {
const na = Number((a.match(/slide-(\d+)\.png/) || [])[1] || 0);
const nb = Number((b.match(/slide-(\d+)\.png/) || [])[1] || 0);
return na - nb;
});
if (slidePngs.length === 0) {
console.error(`[slide-images] lecture ${lectureId}: pdftoppm 출력 파일 없음. tmpWorkDir=${tmpWorkDir}`);
}
const urls = [];
for (let i = 0; i < slidePngs.length; i++) {
const srcPath = path.join(tmpWorkDir, slidePngs[i]);
const destName = `slide-${i + 1}.png`;
const destPath = path.join(outDir, destName);
try {
await fs.copyFile(srcPath, destPath);
urls.push(`/uploads/slides/${lectureId}/${destName}`);
} catch (err) {
console.error(`[slide-images] lecture ${lectureId}: 파일 복사 실패 ${slidePngs[i]}:`, err.message);
break;
}
}
await safeRemove(tmpWorkDir);
if (urls.length > 0) {
console.log(`[slide-images] lecture ${lectureId}: ${urls.length}장 슬라이드 이미지 생성 완료`);
}
return urls.length > 0 ? urls : null;
};
const getSlideImageUrls = (lectureId) => {
const dir = path.join(SLIDES_DIR, lectureId);
if (!fsSync.existsSync(dir)) return [];
const files = fsSync.readdirSync(dir)
.filter((n) => /^slide-\d+\.png$/.test(n))
.sort((a, b) => {
const na = Number((a.match(/slide-(\d+)\.png/) || [])[1] || 0);
const nb = Number((b.match(/slide-(\d+)\.png/) || [])[1] || 0);
return na - nb;
});
return files.map((f) => `/uploads/slides/${lectureId}/${f}`);
};
const AI_SUCCESS_SLIDES_SUBDIR = "ai-success-slides";
function safeAiSuccessSlug(slug) {
return (
String(slug || "")
.replace(/[^a-zA-Z0-9\-_]/g, "-")
.replace(/-+/g, "-")
.replace(/^-|-$/g, "") || "story"
);
}
/** `/public/.../file.pdf` 또는 절대 URL의 `/public/...` 경로 → 절대 경로 (public 하위 PDF만) */
function resolvePublicPdfPath(pdfUrl) {
const raw = (pdfUrl || "").trim();
if (!raw) return null;
let pathname = raw;
if (/^https?:\/\//i.test(raw)) {
try {
pathname = new URL(raw).pathname;
} catch {
return null;
}
} else if (!raw.startsWith("/")) {
if (raw.startsWith("public/")) pathname = `/${raw}`;
else return null;
}
if (!pathname.startsWith("/public/")) return null;
let rel;
try {
rel = decodeURIComponent(pathname).replace(/^\/+/, "");
} catch {
rel = pathname.replace(/^\/+/, "");
}
const abs = path.join(ROOT_DIR, rel);
const normalized = path.normalize(abs);
const publicRoot = path.join(ROOT_DIR, "public");
if (!normalized.startsWith(publicRoot) || !fsSync.existsSync(normalized)) return null;
if (path.extname(normalized).toLowerCase() !== ".pdf") return null;
return normalized;
}
function getAiSuccessSlideImageUrls(slug) {
const safeSlug = safeAiSuccessSlug(slug);
const dir = path.join(UPLOAD_DIR, AI_SUCCESS_SLIDES_SUBDIR, safeSlug);
if (!fsSync.existsSync(dir)) return [];
const files = fsSync
.readdirSync(dir)
.filter((n) => /^slide-\d+\.png$/.test(n))
.sort((a, b) => {
const na = Number((a.match(/slide-(\d+)\.png/) || [])[1] || 0);
const nb = Number((b.match(/slide-(\d+)\.png/) || [])[1] || 0);
return na - nb;
});
return files.map((f) => `/uploads/${AI_SUCCESS_SLIDES_SUBDIR}/${safeSlug}/${f}`);
}
async function generateAiSuccessPdfSlides(pdfPath, slug) {
const safeSlug = safeAiSuccessSlug(slug);
const outDir = path.join(UPLOAD_DIR, AI_SUCCESS_SLIDES_SUBDIR, safeSlug);
await fs.mkdir(outDir, { recursive: true });
const tmpWorkDir = path.join(TMP_DIR, `ai-success-${safeSlug}-${Date.now()}`);
await fs.mkdir(tmpWorkDir, { recursive: true });
const outPrefix = path.join(tmpWorkDir, "slide");
const { ok: okPpm } = await tryExecWithLog(
"pdftoppm",
["-png", "-r", "150", pdfPath, outPrefix],
60000,
`ai-success ${safeSlug} pdftoppm`
);
if (!okPpm) {
await safeRemove(tmpWorkDir);
return null;
}
const slidePngs = fsSync
.readdirSync(tmpWorkDir)
.filter((n) => /^slide-\d+\.png$/.test(n))
.sort((a, b) => {
const na = Number((a.match(/slide-(\d+)\.png/) || [])[1] || 0);
const nb = Number((b.match(/slide-(\d+)\.png/) || [])[1] || 0);
return na - nb;
});
const urls = [];
for (let i = 0; i < slidePngs.length; i++) {
const srcPath = path.join(tmpWorkDir, slidePngs[i]);
const destName = `slide-${i + 1}.png`;
const destPath = path.join(outDir, destName);
try {
await fs.copyFile(srcPath, destPath);
urls.push(`/uploads/${AI_SUCCESS_SLIDES_SUBDIR}/${safeSlug}/${destName}`);
} catch (err) {
console.error(`[ai-success-slides] 복사 실패 ${slidePngs[i]}:`, err?.message || err);
break;
}
}
await safeRemove(tmpWorkDir);
if (urls.length > 0) {
console.log(`[ai-success-slides] ${safeSlug}: ${urls.length}장 생성`);
}
return urls.length > 0 ? urls : null;
}
async function ensureAiSuccessStorySlides(slug, pdfUrl) {
const pdfPath = resolvePublicPdfPath(pdfUrl);
if (!pdfPath) {
return { urls: [], slidesError: true };
}
const existing = getAiSuccessSlideImageUrls(slug);
if (existing.length > 0) {
return { urls: existing, slidesError: false };
}
const generated = await generateAiSuccessPdfSlides(pdfPath, slug);
if (!generated || generated.length === 0) {
return { urls: [], slidesError: true };
}
return { urls: generated, slidesError: false };
}
const ensurePptThumbnailFields = (lecture) => {
if (!lecture || lecture.type !== "ppt") return false;
let changed = false;
if (!lecture.thumbnailStatus) {
lecture.thumbnailStatus = lecture.thumbnailUrl ? "ready" : "pending";
changed = true;
}
if (typeof lecture.thumbnailRetryCount !== "number") {
lecture.thumbnailRetryCount = 0;
changed = true;
}
if (!lecture.thumbnailError) {
lecture.thumbnailError = null;
changed = true;
}
if (!lecture.thumbnailUpdatedAt) {
lecture.thumbnailUpdatedAt = lecture.createdAt || new Date().toISOString();
changed = true;
}
return changed;
};
const enqueueThumbnailJob = (lectureId, options = {}) => {
const force = options.force === true;
const reason = options.reason || "manual";
const persist = options.persist !== false;
if (!lectureId) return false;
if (queuedLectureIds.has(lectureId)) return false;
thumbnailQueue.push({ lectureId, force, reason, enqueuedAt: Date.now() });
queuedLectureIds.add(lectureId);
appendThumbnailEvent({
type: "enqueue",
lectureId,
reason,
force,
queueSizeAfter: thumbnailQueue.length,
}).catch(() => {
// Ignore event logging failure.
});
if (persist) {
writeThumbnailJobDb().catch(() => {
// Ignore queue persistence failure.
});
}
processThumbnailQueue();
return true;
};
const processThumbnailQueue = async () => {
if (thumbnailWorkerRunning) return;
thumbnailWorkerRunning = true;
while (thumbnailQueue.length > 0) {
const job = thumbnailQueue.shift();
queuedLectureIds.delete(job.lectureId);
await writeThumbnailJobDb().catch(() => {
// Ignore queue persistence failure.
});
try {
const startedAt = Date.now();
const lectures = await readLectureDb();
const lecture = lectures.find((item) => item.id === job.lectureId);
if (!lecture || lecture.type !== "ppt" || !lecture.fileName) continue;
ensurePptThumbnailFields(lecture);
if (!job.force && lecture.thumbnailStatus === "ready" && lecture.thumbnailUrl) {
continue;
}
lecture.thumbnailStatus = "processing";
lecture.thumbnailError = null;
lecture.thumbnailUpdatedAt = new Date().toISOString();
await writeLectureDb(lectures);
await appendThumbnailEvent({
type: "start",
lectureId: lecture.id,
lectureTitle: lecture.title,
reason: job.reason,
force: job.force,
queueSizeAfter: thumbnailQueue.length,
}).catch(() => {
// Ignore event logging failure.
});
const filePath = path.join(UPLOAD_DIR, lecture.fileName);
const newThumb = await generatePptThumbnail(filePath, lecture.id);
if (newThumb) {
lecture.thumbnailUrl = newThumb;
lecture.thumbnailStatus = "ready";
lecture.thumbnailError = null;
} else {
lecture.thumbnailStatus = "failed";
lecture.thumbnailError = "썸네일 생성 도구 실행 실패 또는 미설치";
lecture.thumbnailRetryCount = (lecture.thumbnailRetryCount || 0) + 1;
}
lecture.thumbnailUpdatedAt = new Date().toISOString();
await writeLectureDb(lectures);
await appendThumbnailEvent({
type: lecture.thumbnailStatus === "ready" ? "success" : "failed",
lectureId: lecture.id,
lectureTitle: lecture.title,
reason: job.reason,
force: job.force,
retryCount: lecture.thumbnailRetryCount || 0,
error: lecture.thumbnailError || null,
durationMs: Date.now() - startedAt,
}).catch(() => {
// Ignore event logging failure.
});
if (
lecture.thumbnailStatus === "failed" &&
lecture.thumbnailRetryCount <= THUMBNAIL_MAX_RETRY &&
ENABLE_PPT_THUMBNAIL
) {
setTimeout(() => {
enqueueThumbnailJob(lecture.id, { force: true, reason: "auto-retry" });
}, THUMBNAIL_RETRY_DELAY_MS);
}
} catch (error) {
await appendThumbnailEvent({
type: "worker-error",
lectureId: job.lectureId,
reason: job.reason,
force: job.force,
error: error?.message || "unknown worker error",
}).catch(() => {
// Ignore event logging failure.
});
// Keep queue worker alive for next jobs.
}
}
thumbnailWorkerRunning = false;
};
const removeQueuedThumbnailJobs = async (lectureId) => {
if (!lectureId) return;
let changed = false;
for (let i = thumbnailQueue.length - 1; i >= 0; i -= 1) {
if (thumbnailQueue[i].lectureId === lectureId) {
thumbnailQueue.splice(i, 1);
changed = true;
}
}
if (changed) {
queuedLectureIds.delete(lectureId);
await writeThumbnailJobDb().catch(() => {
// Ignore queue persistence failure.
});
}
};
const seedResourceLectures = async () => {
let lectures = await readLectureDb();
if (lectures.length > 0) return;
if (!fsSync.existsSync(RESOURCES_LECTURE_DIR)) return;
const files = await fs.readdir(RESOURCES_LECTURE_DIR);
const pptxFiles = files.filter((name) => path.extname(name).toLowerCase() === ".pptx");
for (const fileName of pptxFiles) {
const source = path.join(RESOURCES_LECTURE_DIR, fileName);
const targetName = `${Date.now()}-${uuidv4()}${path.extname(fileName)}`;
const target = path.join(UPLOAD_DIR, targetName);
await fs.copyFile(source, target);
lectures.push({
id: uuidv4(),
type: "ppt",
listSection: "learning",
title: path.basename(fileName, path.extname(fileName)),
description: "초기 샘플 PPT 강의",
tags: ["샘플", "PPT"],
fileName: targetName,
originalName: fileName,
createdAt: new Date().toISOString(),
});
const inserted = lectures[lectures.length - 1];
const meta = await getPptxMeta(target);
inserted.previewTitle = meta.previewTitle;
inserted.slideCount = meta.slideCount;
inserted.thumbnailUrl = null;
inserted.thumbnailStatus = "pending";
inserted.thumbnailRetryCount = 0;
inserted.thumbnailError = null;
inserted.thumbnailUpdatedAt = new Date().toISOString();
enqueueThumbnailJob(inserted.id, { force: false, reason: "seed" });
}
await writeLectureDb(lectures);
};
const ensureBootstrap = async () => {
await initPostgres();
await thumbnailEventsStore
.migrateThumbnailEventsFromJson(pgPool, THUMBNAIL_EVENT_DB_PATH, THUMBNAIL_EVENT_KEEP)
.catch((e) => console.warn("thumbnail-events migration:", e?.message || e));
await fs.mkdir(DATA_DIR, { recursive: true });
await fs.mkdir(TMP_DIR, { recursive: true });
await fs.mkdir(UPLOAD_DIR, { recursive: true });
await fs.mkdir(THUMBNAIL_DIR, { recursive: true });
if (!pgPool && !fsSync.existsSync(LECTURE_DB_PATH)) {
await writeLectureDb([]);
}
if (!pgPool && !fsSync.existsSync(AX_ASSIGNMENTS_DB_PATH)) {
await fs.writeFile(AX_ASSIGNMENTS_DB_PATH, "[]", "utf-8");
}
if (!fsSync.existsSync(THUMBNAIL_JOB_DB_PATH)) {
await fs.writeFile(THUMBNAIL_JOB_DB_PATH, "[]", "utf-8");
}
if (!pgPool && !fsSync.existsSync(THUMBNAIL_EVENT_DB_PATH)) {
await fs.writeFile(THUMBNAIL_EVENT_DB_PATH, "[]", "utf-8");
}
await seedResourceLectures();
const lectures = await readLectureDb();
let changed = false;
for (const lecture of lectures) {
if (lecture.type !== "ppt") continue;
if (ensurePptThumbnailFields(lecture)) changed = true;
if (lecture.thumbnailStatus === "processing") {
lecture.thumbnailStatus = "pending";
lecture.thumbnailUpdatedAt = new Date().toISOString();
changed = true;
}
if (lecture.thumbnailStatus === "pending") {
enqueueThumbnailJob(lecture.id, { force: false, reason: "bootstrap" });
}
if (
lecture.thumbnailStatus === "failed" &&
lecture.thumbnailRetryCount < THUMBNAIL_MAX_RETRY &&
ENABLE_PPT_THUMBNAIL
) {
lecture.thumbnailStatus = "pending";
lecture.thumbnailError = null;
lecture.thumbnailUpdatedAt = new Date().toISOString();
changed = true;
enqueueThumbnailJob(lecture.id, { force: true, reason: "bootstrap-retry" });
}
}
const persistedJobs = await readThumbnailJobDb();
for (const job of persistedJobs) {
if (!job?.lectureId) continue;
enqueueThumbnailJob(job.lectureId, {
force: job.force === true,
reason: job.reason || "restored",
persist: false,
});
}
await writeThumbnailJobDb().catch(() => {
// Ignore queue persistence failure.
});
if (changed) await writeLectureDb(lectures);
};
function normalizeLectureSearchText(s) {
try {
return String(s || "")
.normalize("NFC")
.toLowerCase();
} catch {
return String(s || "").toLowerCase();
}
}
/** "클로드" 검색 시 본문의 "Claude", "claude" 등과도 매칭되도록 보강 */
function lectureSearchQueryVariants(qNorm) {
if (!qNorm) return [""];
const v = new Set([qNorm]);
if (qNorm.includes("클로드")) v.add(qNorm.replace(/클로드/g, "claude"));
if (qNorm.includes("claude")) v.add(qNorm.replace(/claude/g, "클로드"));
return [...v];
}
/**
* 강의 검색어(q) 매칭: title, description, previewTitle, PPT/PDF는 originalName(확장자 제외).
* 한글 NFC 정규화 + 클로드/claude 상호 보강.
*/
function lectureMatchesSearchQuery(lecture, qNorm) {
if (!qNorm) return true;
const parts = [lecture.title || "", lecture.description || ""];
const pt = (lecture.previewTitle || "").trim();
if (pt && pt !== "제목 없음" && pt !== "미리보기 생성 실패") {
parts.push(pt);
}
if (lecture.type === "ppt" || lecture.type === "video") {
const orig = (lecture.originalName || "").replace(/\.[^.]+$/, "");
if (orig) parts.push(orig);
}
if (lecture.type === "link" || lecture.type === "news") {
const u = (lecture.newsUrl || "").trim();
if (u) parts.push(u);
}
const haystack = normalizeLectureSearchText(parts.join(" "));
return lectureSearchQueryVariants(qNorm).some((variant) => haystack.includes(variant));
}
const buildLectureListContext = async (req, options = {}) => {
const { basePath = "/learning", forAdmin = false } = options;
const page = Math.max(Number(req.query.page) || 1, 1);
const q = (req.query.q || "").toString().trim();
const type = (req.query.type || "all").toString();
const tag = (req.query.tag || "").toString().trim();
const category = (req.query.category || "").toString().trim();
let tokenVal = req.query.token;
if (Array.isArray(tokenVal)) tokenVal = tokenVal[0];
const token = (tokenVal != null ? String(tokenVal) : "").trim();
const retryQueued = Math.max(Number(req.query.retryQueued) || 0, 0);
const eventsCleared = req.query.eventsCleared === "1" || req.query.eventsCleared === "true";
let lectures = await readLectureDb();
let hasMutated = false;
for (const lecture of lectures) {
if (!Array.isArray(lecture.tags)) {
lecture.tags = [];
hasMutated = true;
}
const needsMeta =
lecture.type === "ppt" &&
(lecture.previewTitle === "미리보기 생성 실패" ||
!lecture.previewTitle ||
typeof lecture.slideCount !== "number");
if (needsMeta) {
try {
const filePath = path.join(UPLOAD_DIR, lecture.fileName);
const ext = path.extname(lecture.fileName || "").toLowerCase();
const meta = ext === ".pdf" ? await getPdfMeta(filePath) : await getPptxMeta(filePath);
lecture.previewTitle = meta.previewTitle || (ext === ".pdf" ? "PDF 문서" : "");
lecture.slideCount = meta.slideCount;
hasMutated = true;
} catch {
lecture.previewTitle = lecture.previewTitle || "미리보기 생성 실패";
lecture.slideCount = typeof lecture.slideCount === "number" ? lecture.slideCount : 0;
hasMutated = true;
}
}
if (lecture.type === "ppt") {
if (ensurePptThumbnailFields(lecture)) hasMutated = true;
if (lecture.thumbnailStatus === "pending" && lecture.fileName) {
enqueueThumbnailJob(lecture.id, { force: false, reason: "list-view" });
}
}
}
if (hasMutated) await writeLectureDb(lectures);
/** 학습센터 단일 목록: learning + (구)뉴스 섹션으로 등록된 항목 포함 */
const inSection = (lec) => lec.listSection === "learning" || lec.listSection === "news";
const sectionLectures = lectures.filter(inSection);
const availableTags = [...new Set(sectionLectures.flatMap((lecture) => lecture.tags || []))].sort((a, b) =>
a.localeCompare(b, "ko-KR")
);
const ordered = [...sectionLectures].sort((a, b) => new Date(b.createdAt) - new Date(a.createdAt));
const qNorm = q ? normalizeLectureSearchText(q) : "";
const filtered = ordered.filter((lecture) => {
if (type !== "all" && lecture.type !== type) return false;
if (tag && !(lecture.tags || []).includes(tag)) return false;
if (category && !(lecture.tags || []).includes(category)) return false;
if (!q) return true;
return lectureMatchesSearchQuery(lecture, qNorm);
});
const totalCount = filtered.length;
const totalPages = Math.max(Math.ceil(totalCount / PAGE_SIZE), 1);
const currentPage = Math.min(page, totalPages);
const start = (currentPage - 1) * PAGE_SIZE;
const pageItems = filtered.slice(start, start + PAGE_SIZE);
for (const lec of pageItems) {
const st = lec.thumbnailStatus || "pending";
if (
(lec.type === "link" || lec.type === "news") &&
lec.newsUrl &&
!lec.thumbnailUrl &&
st !== "failed" &&
st !== "ready" &&
!linkOgPreviewScheduledIds.has(lec.id)
) {
linkOgPreviewScheduledIds.add(lec.id);
scheduleLinkOgPreviewFetch(lec.id, lec.newsUrl);
}
}
const isValidToken =
forAdmin && ADMIN_TOKEN.length > 0 && token.length > 0 && token === ADMIN_TOKEN;
const tokenForQuery = isValidToken ? token : "";
const makeQueryForPath = (nextPage) => {
const params = new URLSearchParams();
if (q) params.set("q", q);
if (type && type !== "all") params.set("type", type);
if (tag) params.set("tag", tag);
if (category) params.set("category", category);
if (forAdmin && tokenForQuery) params.set("token", tokenForQuery);
if (nextPage && nextPage > 1) params.set("page", String(nextPage));
return params.toString();
};
const returnQuery = makeQueryForPath(currentPage);
const prevQuery = makeQueryForPath(currentPage - 1);
const nextQuery = makeQueryForPath(currentPage + 1);
const paginationData = {
page: currentPage,
totalPages,
totalCount,
hasPrev: currentPage > 1,
hasNext: currentPage < totalPages,
prevQuery,
nextQuery,
pages: Array.from({ length: totalPages }, (_, idx) => {
const p = idx + 1;
return { page: p, query: makeQueryForPath(p), active: p === currentPage };
}),
};
const ctx = {
lectures: pageItems,
filters: { q: q || "", type: type || "all", tag: tag || "", category: category || "" },
availableTags,
pagination: paginationData,
returnQuery,
viewerBasePath: options.viewerBasePath || "/learning",
learningApiPath: options.learningApiPath || "/api/learning/lectures",
adminRegisterHref: options.adminRegisterHref || "/admin",
adminBasePath: options.adminBasePath || "/admin",
navActiveMenu: options.navActiveMenu || "learning",
pageTitle: options.pageTitle || "학습센터",
heroTitle: options.heroTitle || "최신 컨텐츠로 학습하고, 바로 업무에 적용하세요.",
heroDesc:
options.heroDesc ||
"유튜브·PPT·웹 링크를 등록한 뒤, 목록에서 클릭하여 강의를 시청하거나 외부 자료를 열 수 있습니다.",
sectionListHeading: options.sectionListHeading || "등록된 강의",
filterPanelTitle: options.filterPanelTitle,
adminPageTitle: options.adminPageTitle || "학습센터 관리",
};
if (forAdmin) {
ctx.adminMode = isValidToken;
ctx.adminRequested = !!token;
ctx.tokenRaw = isValidToken ? token : "";
ctx.retryQueued = retryQueued;
ctx.eventsCleared = eventsCleared;
ctx.recentEvents = [...(await readThumbnailEventDb())].reverse().slice(0, 12);
const events = await readThumbnailEventDb();
ctx.failureReasons = Object.values(
events.reduce((acc, evt) => {
if (evt.type !== "failed" || !evt.error) return acc;
const key = evt.error;
if (!acc[key]) acc[key] = { reason: key, count: 0, latestAt: evt.at };
acc[key].count += 1;
if (new Date(evt.at).getTime() > new Date(acc[key].latestAt).getTime()) acc[key].latestAt = evt.at;
return acc;
}, {})
).sort((a, b) => b.count - a.count);
ctx.thumbnailStatusSummary = sectionLectures.reduce(
(acc, l) => {
if (l.type !== "ppt") return acc;
const s = l.thumbnailStatus || "pending";
if (s === "ready") acc.ready += 1;
else if (s === "processing") acc.processing += 1;
else if (s === "failed") acc.failed += 1;
else acc.pending += 1;
return acc;
},
{ ready: 0, processing: 0, failed: 0, pending: 0 }
);
ctx.thumbnailQueueInfo = { pending: thumbnailQueue.length, working: thumbnailWorkerRunning, maxRetry: THUMBNAIL_MAX_RETRY };
}
ctx.opsUserEmail = !!(req.res && req.res.locals && req.res.locals.opsUserEmail);
return ctx;
};
app.post("/api/admin/validate-token", (req, res) => {
const token = (req.body?.token ?? "").toString().trim();
const valid =
ADMIN_TOKEN.length > 0 &&
token.length > 0 &&
token === ADMIN_TOKEN;
res.json({ valid: !!valid });
});
app.get("/", (req, res) => res.redirect("/learning"));
app.get("/learning", async (req, res) => {
const ctx = await buildLectureListContext(req, { basePath: "/learning", forAdmin: false });
res.render("learning-viewer", ctx);
});
/** 구 URL 호환 */
app.get("/newsletter", (req, res) => {
res.redirect(302, "/learning");
});
app.get("/newsletter/admin", (req, res) => {
const q = req.url.includes("?") ? req.url.slice(req.url.indexOf("?")) : "";
res.redirect(302, "/admin" + q);
});
app.get("/api/learning/lectures", async (req, res) => {
try {
const ctx = await buildLectureListContext(req, { basePath: "/learning", forAdmin: false });
res.render("learning-lectures-partial", ctx, (err, html) => {
if (err) return res.status(500).json({ error: err?.message || "렌더링 실패" });
res.json({
html: html || "",
hasNext: ctx.pagination?.hasNext || false,
nextPage: (ctx.pagination?.page || 1) + 1,
totalCount: ctx.pagination?.totalCount ?? 0,
});
});
} catch (err) {
res.status(500).json({ error: err?.message || "요청 처리 실패" });
}
});
app.get("/admin/logout", (req, res) => {
res.clearCookie(ADMIN_COOKIE_NAME, { path: "/" });
res.redirect("/learning");
});
/** 관리자: OPS 이메일 인증 로그인 사용자 목록(이메일·최근 접속일) */
app.get("/admin/users", async (req, res) => {
const token = (req.query.token != null ? String(req.query.token) : "").trim();
const cookieToken = (req.cookies?.[ADMIN_COOKIE_NAME] || "").trim();
const isValidUrlToken =
ADMIN_TOKEN.length > 0 && token.length > 0 && token === ADMIN_TOKEN;
const isValidCookie =
ADMIN_TOKEN.length > 0 && cookieToken.length > 0 && cookieToken === ADMIN_TOKEN;
if (isValidUrlToken) {
res.cookie(ADMIN_COOKIE_NAME, token, {
httpOnly: true,
maxAge: ADMIN_COOKIE_MAX_AGE,
path: "/",
});
} else if (req.cookies?.[ADMIN_COOKIE_NAME] && !isValidCookie) {
res.clearCookie(ADMIN_COOKIE_NAME, { path: "/" });
}
const allowed = isValidUrlToken || isValidCookie;
if (!allowed) {
return res.redirect("/learning");
}
let users = [];
let dbError = null;
const pgConnected = ENABLE_POSTGRES && !!pgPool;
try {
if (pgPool) {
const r = await pgPool.query(
`SELECT email, last_login_at, first_seen_at, login_count
FROM ops_email_users
ORDER BY last_login_at DESC NULLS LAST`
);
users = (r.rows || []).map((row) => ({
email: row.email,
lastLoginAt: row.last_login_at,
firstSeenAt: row.first_seen_at,
loginCount: row.login_count,
}));
}
} catch (err) {
console.error("[admin/users]", err);
dbError = err?.message || "조회 실패";
}
res.render("admin-users", {
adminMode: true,
activeMenu: "admin-users",
users,
dbError,
pgConnected,
});
});
app.get("/admin", async (req, res) => {
const ctx = await buildLectureListContext(req, {
basePath: "/admin",
forAdmin: true,
adminBasePath: "/admin",
viewerBasePath: "/learning",
navActiveMenu: "learning",
});
const token = (req.query.token != null ? String(req.query.token) : "").trim();
const cookieToken = (req.cookies?.[ADMIN_COOKIE_NAME] || "").trim();
const isValidUrlToken =
ADMIN_TOKEN.length > 0 && token.length > 0 && token === ADMIN_TOKEN;
const isValidCookie =
ADMIN_TOKEN.length > 0 && cookieToken.length > 0 && cookieToken === ADMIN_TOKEN;
if (isValidUrlToken) {
res.cookie(ADMIN_COOKIE_NAME, token, {
httpOnly: true,
maxAge: ADMIN_COOKIE_MAX_AGE,
path: "/",
});
} else if (isValidCookie) {
ctx.adminMode = true;
ctx.tokenRaw = cookieToken;
} else if (req.cookies?.[ADMIN_COOKIE_NAME]) {
res.clearCookie(ADMIN_COOKIE_NAME, { path: "/" });
res.locals.adminMode = false;
}
res.render("learning-admin", ctx);
});
app.post("/lectures/youtube", async (req, res) => {
const token = (req.body.token || "").toString().trim();
if (token !== ADMIN_TOKEN) {
res.status(403).send("관리자 토큰이 필요합니다.");
return;
}
const { title, description, youtubeUrl, tags, category } = req.body;
if (!title?.trim() || !youtubeUrl?.trim()) {
res.status(400).send("제목과 유튜브 링크는 필수입니다.");
return;
}
if (!isYoutubeUrl(youtubeUrl)) {
res.status(400).send("유효한 유튜브 링크를 입력해주세요.");
return;
}
const lectures = await readLectureDb();
lectures.push({
id: uuidv4(),
type: "youtube",
listSection: "learning",
title: title.trim(),
description: (description || "").trim(),
tags: mergeTagsWithCategory(tags, category),
youtubeUrl: youtubeUrl.trim(),
createdAt: new Date().toISOString(),
});
await writeLectureDb(lectures);
const returnTo = (req.body.returnTo || "").toString().trim();
res.redirect(returnTo.startsWith("/") ? returnTo : "/admin");
});
app.post("/lectures/ppt", upload.single("pptFile"), async (req, res) => {
const token = (req.body.token || "").toString().trim();
if (token !== ADMIN_TOKEN) {
res.status(403).send("관리자 토큰이 필요합니다.");
return;
}
const { title, description, tags, category } = req.body;
if (!req.file) {
res.status(400).send("PDF 또는 PPT 파일이 필요합니다.");
return;
}
if (!title?.trim()) {
res.status(400).send("제목은 필수입니다.");
return;
}
const filePath = path.join(UPLOAD_DIR, req.file.filename);
const ext = path.extname(req.file.filename).toLowerCase();
let pptMeta = { previewTitle: "", slideCount: 0 };
try {
if (ext === ".pdf") {
pptMeta = await getPdfMeta(filePath);
} else {
pptMeta = await getPptxMeta(filePath);
}
} catch {
// Keep defaults; upload still succeeds even if metadata parsing fails.
}
const lectures = await readLectureDb();
const lectureId = uuidv4();
lectures.push({
id: lectureId,
type: "ppt",
listSection: "learning",
title: title.trim(),
description: (description || "").trim(),
tags: mergeTagsWithCategory(tags, category),
fileName: req.file.filename,
originalName: req.file.originalname,
previewTitle: pptMeta.previewTitle,
slideCount: pptMeta.slideCount,
thumbnailUrl: null,
thumbnailStatus: "pending",
thumbnailRetryCount: 0,
thumbnailError: null,
thumbnailUpdatedAt: new Date().toISOString(),
createdAt: new Date().toISOString(),
});
await writeLectureDb(lectures);
enqueueThumbnailJob(lectureId, { force: false, reason: "upload" });
if (ENABLE_PPT_THUMBNAIL) {
generateSlideImages(filePath, lectureId).catch((err) => {
console.error("[slide-images] 업로드 후 생성 실패:", err?.message || err);
});
}
const returnTo = (req.body.returnTo || "").toString().trim();
res.redirect(returnTo.startsWith("/") ? returnTo : "/admin");
});
app.post(
"/lectures/video",
(req, res, next) => {
uploadVideo.single("videoFile")(req, res, (err) => {
if (err) {
res.status(400).send(err.message || "동영상 업로드에 실패했습니다.");
return;
}
next();
});
},
async (req, res) => {
const token = (req.body.token || "").toString().trim();
if (token !== ADMIN_TOKEN) {
res.status(403).send("관리자 토큰이 필요합니다.");
return;
}
const { title, description, tags, category } = req.body;
if (!req.file) {
res.status(400).send("동영상 파일이 필요합니다.");
return;
}
if (!title?.trim()) {
res.status(400).send("제목은 필수입니다.");
return;
}
const lectures = await readLectureDb();
const lectureId = uuidv4();
const createdAt = new Date().toISOString();
lectures.push({
id: lectureId,
type: "video",
listSection: "learning",
title: title.trim(),
description: (description || "").trim(),
tags: mergeTagsWithCategory(tags, category),
fileName: req.file.filename,
originalName: req.file.originalname,
previewTitle: null,
slideCount: 0,
youtubeUrl: null,
thumbnailUrl: null,
thumbnailStatus: "ready",
thumbnailRetryCount: 0,
thumbnailError: null,
thumbnailUpdatedAt: createdAt,
createdAt,
});
await writeLectureDb(lectures);
const returnTo = (req.body.returnTo || "").toString().trim();
res.redirect(returnTo.startsWith("/") ? returnTo : "/admin");
}
);
/** 학습센터: 유튜브가 아닌 일반 http(s) 링크를 카드로 등록 (news_url 컬럼 사용) */
app.post("/lectures/link", async (req, res) => {
const token = (req.body.token || "").toString().trim();
if (token !== ADMIN_TOKEN) {
res.status(403).send("관리자 토큰이 필요합니다.");
return;
}
const { title, description, tags, category } = req.body;
const linkUrl = (req.body.linkUrl || "").toString().trim();
if (!title?.trim() || !linkUrl) {
res.status(400).send("제목과 URL은 필수입니다.");
return;
}
if (!isHttpUrl(linkUrl)) {
res.status(400).send("유효한 http(s) URL을 입력해주세요.");
return;
}
if (isYoutubeUrl(linkUrl)) {
res.status(400).send("유튜브 링크는 «유튜브 강의 등록»을 이용해주세요.");
return;
}
const lectures = await readLectureDb();
const lectureId = uuidv4();
const createdAt = new Date().toISOString();
lectures.push({
id: lectureId,
type: "link",
listSection: "learning",
title: title.trim(),
description: (description || "").trim(),
tags: mergeTagsWithCategory(tags, category),
newsUrl: linkUrl,
thumbnailUrl: null,
thumbnailStatus: "pending",
thumbnailRetryCount: 0,
thumbnailError: null,
thumbnailUpdatedAt: createdAt,
createdAt,
});
await writeLectureDb(lectures);
scheduleLinkOgPreviewFetch(lectureId, linkUrl);
const returnTo = (req.body.returnTo || "").toString().trim();
res.redirect(returnTo.startsWith("/") ? returnTo : "/admin");
});
app.post("/lectures/:id/delete", async (req, res) => {
const { token, returnTo } = req.body;
if ((token || "").trim() !== ADMIN_TOKEN) {
res.status(403).send("관리자 토큰이 유효하지 않습니다.");
return;
}
const lectures = await readLectureDb();
const index = lectures.findIndex((item) => item.id === req.params.id);
if (index < 0) {
res.status(404).send("삭제할 강의를 찾을 수 없습니다.");
return;
}
const target = lectures[index];
await removeQueuedThumbnailJobs(target.id);
const storedFileTypes = ["ppt", "video"];
if (storedFileTypes.includes(target.type) && target.fileName) {
const filePath = path.join(UPLOAD_DIR, target.fileName);
try {
await fs.unlink(filePath);
} catch {
// Ignore file delete failures to keep metadata consistent.
}
if (target.type === "ppt") {
if (target.thumbnailUrl) {
const thumbPath = path.join(ROOT_DIR, target.thumbnailUrl.replace(/^\//, ""));
try {
await fs.unlink(thumbPath);
} catch {
// Ignore thumbnail delete failures.
}
}
const slidesDir = path.join(SLIDES_DIR, target.id);
try {
await fs.rm(slidesDir, { recursive: true, force: true });
} catch {
// Ignore slide images delete failures.
}
}
}
lectures.splice(index, 1);
await writeLectureDb(lectures);
const safeReturn = (returnTo || "").toString().trim();
if (safeReturn.startsWith("/")) {
res.redirect(safeReturn);
return;
}
if (safeReturn.startsWith("?")) {
res.redirect(`/admin${safeReturn}`);
return;
}
res.redirect("/admin");
});
app.post("/lectures/:id/thumbnail/regenerate", async (req, res) => {
const { token, returnTo } = req.body;
if ((token || "").trim() !== ADMIN_TOKEN) {
res.status(403).send("관리자 토큰이 유효하지 않습니다.");
return;
}
const lectures = await readLectureDb();
const lecture = lectures.find((item) => item.id === req.params.id);
if (!lecture) {
res.status(404).send("강의를 찾을 수 없습니다.");
return;
}
if (lecture.type !== "ppt" || !lecture.fileName) {
res.status(400).send("PPT 강의만 썸네일 재생성이 가능합니다.");
return;
}
ensurePptThumbnailFields(lecture);
lecture.thumbnailStatus = "pending";
lecture.thumbnailError = null;
lecture.thumbnailRetryCount = 0;
lecture.thumbnailUpdatedAt = new Date().toISOString();
await writeLectureDb(lectures);
enqueueThumbnailJob(lecture.id, { force: true, reason: "manual-regenerate" });
const safeReturn = (returnTo || "").toString().trim();
if (safeReturn.startsWith("/")) {
res.redirect(safeReturn);
return;
}
if (safeReturn.startsWith("?")) {
res.redirect(`/admin${safeReturn}`);
return;
}
res.redirect("/admin");
});
app.post("/lectures/:id/slides/regenerate", async (req, res) => {
const { token, returnTo } = req.body;
if ((token || "").trim() !== ADMIN_TOKEN) {
res.status(403).send("관리자 토큰이 유효하지 않습니다.");
return;
}
const lectures = await readLectureDb();
const lecture = lectures.find((item) => item.id === req.params.id);
if (!lecture) {
res.status(404).send("강의를 찾을 수 없습니다.");
return;
}
if (lecture.type !== "ppt" || !lecture.fileName) {
res.status(400).send("PDF/PowerPoint 강의만 슬라이드 이미지 재생성이 가능합니다.");
return;
}
const filePath = path.join(UPLOAD_DIR, lecture.fileName);
if (!fsSync.existsSync(filePath)) {
res.status(400).send("원본 파일을 찾을 수 없습니다.");
return;
}
const outDir = path.join(SLIDES_DIR, lecture.id);
try {
await fs.rm(outDir, { recursive: true, force: true });
} catch {
// Ignore
}
const urls = await generateSlideImages(filePath, lecture.id);
if (urls && urls.length > 0) {
res.redirect(`/lectures/${lecture.id}`);
} else {
res.redirect(`/lectures/${lecture.id}?slidesError=1`);
}
});
app.post("/thumbnails/retry-failed", async (req, res) => {
const { token, returnTo } = req.body;
if ((token || "").trim() !== ADMIN_TOKEN) {
res.status(403).send("관리자 토큰이 유효하지 않습니다.");
return;
}
const lectures = await readLectureDb();
let queued = 0;
for (const lecture of lectures) {
if (lecture.type !== "ppt") continue;
ensurePptThumbnailFields(lecture);
if (lecture.thumbnailStatus === "failed") {
lecture.thumbnailStatus = "pending";
lecture.thumbnailError = null;
lecture.thumbnailRetryCount = 0;
lecture.thumbnailUpdatedAt = new Date().toISOString();
if (enqueueThumbnailJob(lecture.id, { force: true, reason: "bulk-retry" })) {
queued += 1;
}
}
}
await writeLectureDb(lectures);
const safeReturn = (returnTo || "").toString().trim();
if (safeReturn.startsWith("/")) {
const sep = safeReturn.includes("?") ? "&" : "?";
res.redirect(`${safeReturn}${sep}retryQueued=${queued}`);
return;
}
if (safeReturn.startsWith("?")) {
const joiner = safeReturn.length > 1 ? "&" : "";
res.redirect(`/admin${safeReturn}${joiner}retryQueued=${queued}`);
return;
}
res.redirect(`/admin?retryQueued=${queued}`);
});
app.post("/thumbnails/events/clear", async (req, res) => {
const { token, returnTo } = req.body;
if ((token || "").trim() !== ADMIN_TOKEN) {
res.status(403).send("관리자 토큰이 유효하지 않습니다.");
return;
}
await writeThumbnailEventDb([]);
const safeReturn = (returnTo || "").toString().trim();
if (safeReturn.startsWith("/")) {
const sep = safeReturn.includes("?") ? "&" : "?";
res.redirect(`${safeReturn}${sep}eventsCleared=1`);
return;
}
if (safeReturn.startsWith("?")) {
const joiner = safeReturn.length > 1 ? "&" : "";
res.redirect(`/admin${safeReturn}${joiner}eventsCleared=1`);
return;
}
res.redirect("/admin?eventsCleared=1");
});
app.get("/admin/thumbnail-events", async (req, res) => {
const token = (req.query.token || "").toString();
if (token !== ADMIN_TOKEN) {
res.status(403).send("관리자 토큰이 유효하지 않습니다.");
return;
}
const page = Math.max(Number(req.query.page) || 1, 1);
const limit = Math.min(Math.max(Number(req.query.limit) || THUMBNAIL_EVENT_PAGE_SIZE, 10), 200);
const filter = buildEventFilter(req.query);
const events = await readThumbnailEventDb();
const filtered = [...events].reverse().filter(filter.matches);
const totalCount = filtered.length;
const totalPages = Math.max(Math.ceil(totalCount / limit), 1);
const currentPage = Math.min(page, totalPages);
const start = (currentPage - 1) * limit;
const pageItems = filtered.slice(start, start + limit);
const makeQuery = (nextPage) => {
const params = new URLSearchParams();
params.set("token", token);
if (filter.eventType !== "all") params.set("eventType", filter.eventType);
if (filter.lectureId) params.set("lectureId", filter.lectureId);
if (filter.reason) params.set("reason", filter.reason);
if (filter.from) params.set("from", filter.from);
if (filter.to) params.set("to", filter.to);
if (limit !== THUMBNAIL_EVENT_PAGE_SIZE) params.set("limit", String(limit));
if (nextPage > 1) params.set("page", String(nextPage));
return params.toString();
};
const pagination = {
page: currentPage,
totalPages,
totalCount,
hasPrev: currentPage > 1,
hasNext: currentPage < totalPages,
prevQuery: makeQuery(currentPage - 1),
nextQuery: makeQuery(currentPage + 1),
pages: Array.from({ length: totalPages }, (_, idx) => {
const p = idx + 1;
return { page: p, query: makeQuery(p), active: p === currentPage };
}),
};
const csvQuery = (() => {
const params = new URLSearchParams(makeQuery(1));
params.delete("page");
return params.toString();
})();
const eventsCleared = req.query.eventsCleared === "1" || req.query.eventsCleared === "true";
res.render("admin-thumbnail-events", {
token,
filters: {
eventType: filter.eventType,
lectureId: filter.lectureId,
reason: filter.reason,
from: filter.from,
to: filter.to,
limit,
},
events: pageItems,
pagination,
csvQuery,
eventsCleared,
});
});
app.get("/admin/thumbnail-events.csv", async (req, res) => {
const token = (req.query.token || "").toString();
if (token !== ADMIN_TOKEN) {
res.status(403).send("관리자 토큰이 유효하지 않습니다.");
return;
}
const filter = buildEventFilter(req.query);
const events = await readThumbnailEventDb();
const filtered = [...events].reverse().filter(filter.matches);
const header = ["id", "at", "type", "lectureId", "lectureTitle", "reason", "force", "retryCount", "durationMs", "error"];
const rows = filtered.map((evt) =>
[
evt.id,
evt.at,
evt.type,
evt.lectureId || "",
evt.lectureTitle || "",
evt.reason || "",
evt.force === true ? "true" : "false",
evt.retryCount ?? "",
evt.durationMs ?? "",
evt.error || "",
]
.map(escapeCsv)
.join(",")
);
const csv = [header.join(","), ...rows].join("\n");
res.setHeader("Content-Type", "text/csv; charset=utf-8");
res.setHeader("Content-Disposition", `attachment; filename="thumbnail-events-${Date.now()}.csv"`);
res.send(csv);
});
app.get("/api/queue/metrics", async (req, res) => {
const token = (req.query.token || "").toString();
if (token !== ADMIN_TOKEN) {
res.status(403).json({ message: "관리자 토큰이 유효하지 않습니다." });
return;
}
const lectures = await readLectureDb();
const events = await readThumbnailEventDb();
const pptLectures = lectures.filter((lecture) => lecture.type === "ppt");
const statusCounts = pptLectures.reduce(
(acc, lecture) => {
const status = lecture.thumbnailStatus || "pending";
if (status === "ready") acc.ready += 1;
else if (status === "processing") acc.processing += 1;
else if (status === "failed") acc.failed += 1;
else acc.pending += 1;
return acc;
},
{ ready: 0, processing: 0, pending: 0, failed: 0 }
);
const recentFailures = [...events]
.filter((evt) => evt.type === "failed")
.reverse()
.slice(0, 10)
.map((evt) => ({
at: evt.at,
lectureId: evt.lectureId,
lectureTitle: evt.lectureTitle || null,
reason: evt.reason || null,
error: evt.error || null,
retryCount: evt.retryCount || 0,
}));
res.json({
queue: {
pending: thumbnailQueue.length,
working: thumbnailWorkerRunning,
queuedLectureIds: [...queuedLectureIds],
},
statusCounts,
config: {
maxRetry: THUMBNAIL_MAX_RETRY,
retryDelayMs: THUMBNAIL_RETRY_DELAY_MS,
eventKeep: THUMBNAIL_EVENT_KEEP,
thumbnailEnabled: ENABLE_PPT_THUMBNAIL,
},
recentFailures,
generatedAt: new Date().toISOString(),
});
});
app.get("/api/queue/events-summary", async (req, res) => {
const token = (req.query.token || "").toString();
if (token !== ADMIN_TOKEN) {
res.status(403).json({ message: "관리자 토큰이 유효하지 않습니다." });
return;
}
const hours = Math.min(Math.max(Number(req.query.hours) || 24, 1), 168);
const now = Date.now();
const startMs = now - hours * 60 * 60 * 1000;
const events = await readThumbnailEventDb();
const scoped = events.filter((evt) => {
const atMs = new Date(evt.at || "").getTime();
return Number.isFinite(atMs) && atMs >= startMs;
});
const processed = scoped.filter((evt) => evt.type === "success" || evt.type === "failed");
const successCount = processed.filter((evt) => evt.type === "success").length;
const failedCount = processed.filter((evt) => evt.type === "failed").length;
const processedCount = processed.length;
const failureRate = processedCount > 0 ? failedCount / processedCount : 0;
const durations = processed
.map((evt) => Number(evt.durationMs))
.filter((ms) => Number.isFinite(ms) && ms >= 0);
const avgDurationMs = durations.length
? Math.round(durations.reduce((sum, ms) => sum + ms, 0) / durations.length)
: 0;
const bucketMap = new Map();
for (let i = 0; i < hours; i += 1) {
const bucketStart = startMs + i * 60 * 60 * 1000;
const key = new Date(bucketStart).toISOString().slice(0, 13);
bucketMap.set(key, {
key,
label: `${new Date(bucketStart).getHours()}시`,
processed: 0,
success: 0,
failed: 0,
});
}
for (const evt of processed) {
const atMs = new Date(evt.at || "").getTime();
if (!Number.isFinite(atMs) || atMs < startMs) continue;
const key = new Date(atMs).toISOString().slice(0, 13);
const bucket = bucketMap.get(key);
if (!bucket) continue;
bucket.processed += 1;
if (evt.type === "success") bucket.success += 1;
if (evt.type === "failed") bucket.failed += 1;
}
const buckets = [...bucketMap.values()];
const queue = {
pending: thumbnailQueue.length,
working: thumbnailWorkerRunning,
};
res.json({
windowHours: hours,
generatedAt: new Date().toISOString(),
kpi: {
processedCount,
successCount,
failedCount,
failureRate,
avgDurationMs,
},
queue,
buckets,
});
});
app.get("/lectures/:id/edit", async (req, res) => {
const queryToken = (req.query.token || "").toString().trim();
const cookieToken = (req.cookies?.[ADMIN_COOKIE_NAME] || "").toString().trim();
const isValidQuery = ADMIN_TOKEN.length > 0 && queryToken === ADMIN_TOKEN;
const isValidCookie = ADMIN_TOKEN.length > 0 && cookieToken.length > 0 && cookieToken === ADMIN_TOKEN;
if (!isValidQuery && !isValidCookie) {
res.status(403).send("관리자 토큰이 필요합니다.");
return;
}
const tokenRaw = isValidQuery ? queryToken : cookieToken;
const lectures = await readLectureDb();
const lecture = lectures.find((item) => item.id === req.params.id);
if (!lecture) {
res.status(404).send("강의를 찾을 수 없습니다.");
return;
}
const returnQuery = new URLSearchParams(req.query).toString();
res.render("learning-edit", {
lecture,
tokenRaw,
returnQuery: returnQuery || (tokenRaw ? `token=${encodeURIComponent(tokenRaw)}` : ""),
adminBasePath: "/admin",
viewerBasePath: "/learning",
navActiveMenu: "learning",
});
});
app.post("/lectures/:id/update", async (req, res) => {
const { token, returnTo, title, description, tags, category } = req.body;
const youtubeUrl = req.body.youtubeUrl;
if ((token || "").trim() !== ADMIN_TOKEN) {
res.status(403).send("관리자 토큰이 유효하지 않습니다.");
return;
}
const lectures = await readLectureDb();
const index = lectures.findIndex((item) => item.id === req.params.id);
if (index < 0) {
res.status(404).send("수정할 강의를 찾을 수 없습니다.");
return;
}
const lecture = lectures[index];
if (!title?.trim()) {
res.status(400).send("제목은 필수입니다.");
return;
}
if (lecture.type === "youtube") {
if (!youtubeUrl?.trim()) {
res.status(400).send("유튜브 링크는 필수입니다.");
return;
}
if (!isYoutubeUrl(youtubeUrl)) {
res.status(400).send("유효한 유튜브 링크를 입력해주세요.");
return;
}
lecture.youtubeUrl = youtubeUrl.trim();
}
let scheduleOgAfterWrite = false;
if (lecture.type === "news" || lecture.type === "link") {
const nu = (req.body.newsUrl || "").toString().trim();
if (!nu) {
res.status(400).send(lecture.type === "link" ? "URL은 필수입니다." : "뉴스 URL은 필수입니다.");
return;
}
if (!isHttpUrl(nu)) {
res.status(400).send("유효한 http(s) URL을 입력해주세요.");
return;
}
if (lecture.type === "link" && isYoutubeUrl(nu)) {
res.status(400).send("유튜브 링크는 «유튜브 강의 등록»을 이용해주세요.");
return;
}
const prevUrl = (lecture.newsUrl || "").trim();
lecture.newsUrl = nu;
if (prevUrl !== nu) {
scheduleOgAfterWrite = true;
lecture.thumbnailUrl = null;
lecture.thumbnailStatus = "pending";
lecture.thumbnailError = null;
lecture.thumbnailUpdatedAt = new Date().toISOString();
}
}
lecture.title = title.trim();
lecture.description = (description || "").trim();
lecture.tags = mergeTagsWithCategory(tags, category);
await writeLectureDb(lectures);
if (scheduleOgAfterWrite && (lecture.type === "news" || lecture.type === "link")) {
const nu = (lecture.newsUrl || "").trim();
if (nu && isHttpUrl(nu) && !(lecture.type === "link" && isYoutubeUrl(nu))) {
scheduleLinkOgPreviewFetch(lecture.id, nu);
}
}
const safeReturn = (returnTo || "").toString().trim();
res.redirect(safeReturn.startsWith("/") ? safeReturn : `/admin?${safeReturn || ""}`);
});
app.get("/lectures/:id", async (req, res) => {
const lectures = await readLectureDb();
const lecture = lectures.find((item) => item.id === req.params.id);
if (!lecture) {
res.status(404).send("강의를 찾을 수 없습니다.");
return;
}
if (lecture.type === "youtube") {
res.render("lecture-youtube", {
lecture,
embedUrl: toYoutubeEmbedUrl(lecture.youtubeUrl),
});
return;
}
if (lecture.type === "news" || lecture.type === "link") {
const nu = (lecture.newsUrl || "").trim();
if (nu && isHttpUrl(nu)) {
res.redirect(302, nu);
return;
}
res.render(lecture.type === "news" ? "lecture-news" : "lecture-link", { lecture });
return;
}
if (lecture.type === "video" && lecture.fileName) {
const filePath = path.join(UPLOAD_DIR, lecture.fileName);
if (!fsSync.existsSync(filePath)) {
res.status(404).send("동영상 파일을 찾을 수 없습니다.");
return;
}
const videoSrc = `/uploads/${path.basename(lecture.fileName)}`;
res.render("lecture-video", { lecture, videoSrc });
return;
}
const filePath = path.join(UPLOAD_DIR, lecture.fileName);
const ext = path.extname(lecture.fileName || "").toLowerCase();
let slides =
ext === ".pdf"
? await parsePdfSlides(filePath)
: await parsePptxSlides(filePath);
const slideImageUrls = getSlideImageUrls(lecture.id);
if (slides.length === 0 && slideImageUrls.length > 0) {
slides = slideImageUrls.map(() => ({ title: "", lines: [] }));
}
res.render("lecture-ppt", {
lecture,
slides,
slideImageUrls: slideImageUrls || [],
slidesError: req.query.slidesError === "1",
});
});
app.use((req, res) => {
res.status(404).send(`페이지를 찾을 수 없습니다: ${req.method} ${req.path}`);
});
app.use((error, _, res, __) => {
res.status(400).send(error.message || "요청 처리 중 오류가 발생했습니다.");
});
ensureBootstrap()
.then(() => {
app.listen(PORT, HOST, () => {
const hint = HOST === "0.0.0.0" || HOST === "::" ? "localhost" : HOST;
console.log(`Server started: http://${hint}:${PORT} (listening on ${HOST}:${PORT})`);
});
})
.catch((error) => {
console.error("Failed to bootstrap:", error);
process.exit(1);
});