ai_platform/lib/ai-use-case-submissions.js

/**
 * AI 활용 사례 — 사용자 제출(글쓰기) PostgreSQL 저장
 */
const { v4: uuidv4 } = require("uuid");
const { stripForCount } = require("./strip-for-count");

const PLACEHOLDERS = {
  situation:
    "고객센터에는 반복적인 문의(배송 일정, 환불 절차, 계정 비밀번호 초기화 등)가 하루 수백 건씩 접수되어 상담사의 업무가 과중되고, 응답 지연으로 고객 만족도가 낮아지고 있었습니다.",
  task:
    "반복 문의를 AI로 자동 처리하여 상담사의 업무 부담을 줄이고, 고객 응답 속도와 만족도를 높이는 것이 목표였습니다.",
  action:
    "FAQ 데이터와 기존 상담 이력을 기반으로 AI 챗봇을 구축했습니다.\n주요 질문 유형을 분류하고, 자연어 기반 답변이 가능하도록 학습시켰으며, 복잡한 문의는 상담사에게 자동 이관되도록 설계했습니다.",
  result:
    "전체 문의의 65%를 AI가 자동 처리하게 되었고, 평균 응답 시간이 10분에서 30초 이내로 단축되었습니다. 상담사들은 고난도 고객 대응에 집중할 수 있게 되었고, 고객 만족도는 20% 이상 향상되었습니다.",
};

const MAX_BODY_TOTAL = 10000;
const MAX_THUMB_BYTES = 5 * 1024 * 1024;
const MAX_THUMB_COUNT = 5;
const MAX_ATTACH_BYTES = 20 * 1024 * 1024;
const MAX_ATTACH_COUNT = 1;

/**
 * @param {object} f
 * @returns {{ originalName: string, relativePath: string, size?: number } | null}
 */
function normalizeThumbFileEntry(f) {
  if (!f || typeof f !== "object") return null;
  const rp = String(f.relativePath || f.relative_path || "").trim();
  if (!rp) return null;
  const orig =
    String(f.originalName || f.original_name || rp.split("/").pop() || "thumbnail").trim() ||
    "thumbnail";
  const out = { originalName: orig, relativePath: rp };
  if (typeof f.size === "number" && f.size >= 0) out.size = f.size;
  return out;
}

/**
 * DB row → 썸네일 파일 목록(legacy 단일 경로 포함)
 * @param {object} row
 * @returns {Array<{ originalName: string, relativePath: string, size?: number }>}
 */
function parseThumbnailFiles(row) {
  let files = [];
  try {
    const raw = row && row.thumbnail_files;
    const parsed = typeof raw === "string" ? JSON.parse(raw) : raw;
    if (Array.isArray(parsed)) {
      files = parsed.map(normalizeThumbFileEntry).filter(Boolean);
    }
  } catch {
    files = [];
  }
  if (!files.length && row && row.thumbnail_relative_path) {
    const rp = String(row.thumbnail_relative_path).trim();
    if (rp) {
      files = [
        {
          originalName: rp.split("/").pop() || "thumbnail",
          relativePath: rp,
        },
      ];
    }
  }
  return files;
}

/**
 * @param {Array<{ relativePath?: string }>} files
 * @returns {string | null}
 */
function primaryThumbnailPath(files) {
  if (!Array.isArray(files) || !files.length) return null;
  const rp = String(files[0].relativePath || "").trim();
  return rp || null;
}

function countBodyTotal(row) {
  return (
    stripForCount(row.situation || "") +
    stripForCount(row.task_goal || "") +
    stripForCount(row.action_taken || "") +
    stripForCount(row.result_outcome || "")
  );
}

/**
 * @param {import("pg").Pool} pgPool
 * @param {object} p
 * @param {string} [p.id] - 업로드 디렉터리명과 동일한 UUID(미입력 시 생성)
 * @param {string} p.submitterEmail
 * @param {string} p.title
 * @param {string} p.situation
 * @param {string} p.taskGoal
 * @param {string} p.actionTaken
 * @param {string} p.resultOutcome
 * @param {string[]} p.tags
 * @param {string | null} p.thumbnailRelativePath - 목록 카드용 대표(첫 번째) 경로
 * @param {Array<{ originalName: string, relativePath: string, size?: number }>} p.thumbnailFiles
 * @param {Array<{ originalName: string, relativePath: string }>} p.attachmentFiles
 */
async function insertSubmission(pgPool, p) {
  if (!pgPool) throw new Error("PostgreSQL이 필요합니다.");
  const total = countBodyTotal({
    situation: p.situation,
    task_goal: p.taskGoal,
    action_taken: p.actionTaken,
    result_outcome: p.resultOutcome,
  });
  if (total > MAX_BODY_TOTAL) {
    throw new Error(`본문(4개 합계)은 ${MAX_BODY_TOTAL}자 이하여야 합니다. (현재 ${total}자)`);
  }
  const thumbFiles = Array.isArray(p.thumbnailFiles) ? p.thumbnailFiles.map(normalizeThumbFileEntry).filter(Boolean) : [];
  const primaryThumb = p.thumbnailRelativePath || primaryThumbnailPath(thumbFiles);
  const id = p.id || uuidv4();
  const q = `INSERT INTO ai_use_case_submissions (
    id, submitter_email, title, situation, task_goal, action_taken, result_outcome,
    tags, thumbnail_relative_path, thumbnail_files, attachment_files, created_at, updated_at
  ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10::jsonb, $11::jsonb, NOW(), NOW())`;
  await pgPool.query(q, [
    id,
    p.submitterEmail,
    p.title,
    p.situation || "",
    p.taskGoal || "",
    p.actionTaken || "",
    p.resultOutcome || "",
    p.tags && p.tags.length ? p.tags : [],
    primaryThumb || null,
    JSON.stringify(thumbFiles),
    JSON.stringify(p.attachmentFiles || []),
  ]);
  return { id };
}

const SUBMISSION_DEPT = "일반 제출";
const EXCERPT_MAX = 220;

/**
 * @param {string} situation
 * @param {string} task
 * @param {string} action
 * @param {string} result
 * @param {number} [max]
 */
function buildExcerpt(situation, task, action, result, max) {
  const cap = max == null ? EXCERPT_MAX : max;
  const full = [situation, task, action, result]
    .map((h) => stripForCount(h || ""))
    .filter(Boolean)
    .join(" ")
    .replace(/\s+/g, " ")
    .trim();
  if (!full) return "";
  if (full.length <= cap) return full;
  return full.slice(0, cap) + "…";
}

/**
 * /ai-cases 목록 카드용
 * @param {object} row — pg row
 * @returns {object}
 */
function mapRowToListCard(row) {
  const email = (row.submitter_email || "").trim();
  const author = email && email.indexOf("@") > 0 ? email.split("@")[0] : email || "제출자";
  const created = row.created_at ? new Date(row.created_at) : new Date(0);
  const published = Number.isNaN(created.getTime()) ? "" : created.toISOString().slice(0, 10);
  const cover = primaryThumbnailPath(parseThumbnailFiles(row)) || (row.thumbnail_relative_path || "").trim();
  return {
    _source: "submission",
    submissionId: String(row.id),
    title: (row.title || "").trim() || "제목 없음",
    excerpt: buildExcerpt(row.situation, row.task_goal, row.action_taken, row.result_outcome),
    department: SUBMISSION_DEPT,
    author,
    submitterEmail: email,
    tags: Array.isArray(row.tags) ? row.tags : [],
    publishedAt: published,
    _dateSort: created,
    coverImageUrl: cover,
    viewCount: Number(row.view_count) || 0,
    likeCount: Number(row.like_count) || 0,
  };
}

/**
 * @param {import("pg").Pool} pgPool
 * @param {number} [limit=500]
 */
async function listForPublicList(pgPool, limit) {
  if (!pgPool) return [];
  const lim = Math.min(Math.max(1, limit || 500), 1000);
  const r = await pgPool.query(
    `SELECT
       s.id,
       s.submitter_email,
       s.title,
       s.situation,
       s.task_goal,
       s.action_taken,
       s.result_outcome,
       s.tags,
       s.thumbnail_relative_path,
       s.thumbnail_files,
       s.created_at,
       COALESCE(s.view_count, 0)::int AS view_count,
       (SELECT COUNT(*)::int FROM ai_use_case_submission_likes l WHERE l.submission_id = s.id) AS like_count
     FROM ai_use_case_submissions s
     ORDER BY s.created_at DESC
     LIMIT $1`,
    [lim]
  );
  return (r.rows || []).map(mapRowToListCard);
}

/**
 * @param {import("pg").Pool} pgPool
 * @param {string} id
 */
async function getSubmissionById(pgPool, id) {
  if (!pgPool) return null;
  const r = await pgPool.query(
    `SELECT * FROM ai_use_case_submissions WHERE id = $1 LIMIT 1`,
    [id]
  );
  return r.rows && r.rows[0] ? r.rows[0] : null;
}

/**
 * 편집 권한: 제출자 본인 또는 관리자
 * @param {string} rowSubmitterEmail
 * @param {string | null} requestEmail
 * @param {boolean} isAdmin
 */
function canUserEditSubmission(rowSubmitterEmail, requestEmail, isAdmin) {
  if (isAdmin) return true;
  if (!requestEmail) return false;
  const a = (rowSubmitterEmail || "").trim().toLowerCase();
  const b = String(requestEmail).trim().toLowerCase();
  return Boolean(a && b && a === b);
}

/**
 * 작성 화면 Toast 초기 HTML (DB에 저장된 4섹션 HTML을 그대로 감쌈)
 * @param {string} situation
 * @param {string} task
 * @param {string} action
 * @param {string} result
 */
function buildComposeEditorHtml(situation, task, action, result) {
  const b = (x) => (x && String(x).trim() ? String(x) : "<p><br></p>");
  return (
    '<div class="uc-doc">' +
    '<div id="uc-situation" class="uc-section"><h2>1. Situation (배경)</h2>' +
    b(situation) +
    "</div>" +
    '<div id="uc-task" class="uc-section"><h2>2. Task (과제/목표)</h2>' +
    b(task) +
    "</div>" +
    '<div id="uc-action" class="uc-section"><h2>3. Action (행동)</h2>' +
    b(action) +
    "</div>" +
    '<div id="uc-result" class="uc-section"><h2>4. Result (결과)</h2>' +
    b(result) +
    "</div>" +
    "</div>"
  );
}

/**
 * @param {import("pg").Pool} pgPool
 * @param {object} p
 * @param {string} p.id
 * @param {string} p.title
 * @param {string} p.situation
 * @param {string} p.taskGoal
 * @param {string} p.actionTaken
 * @param {string} p.resultOutcome
 * @param {string[]} p.tags
 * @param {string} p.thumbnailFilesJson — 썸네일 배열 전체 JSON
 * @param {string} p.attachmentFilesJson — json 문자열(배열 전체)
 */
async function updateSubmission(pgPool, p) {
  if (!pgPool) throw new Error("PostgreSQL이 필요합니다.");
  const total = countBodyTotal({
    situation: p.situation,
    task_goal: p.taskGoal,
    action_taken: p.actionTaken,
    result_outcome: p.resultOutcome,
  });
  if (total > MAX_BODY_TOTAL) {
    throw new Error(`본문(4개 합계)은 ${MAX_BODY_TOTAL}자 이하여야 합니다. (현재 ${total}자)`);
  }
  let thumbFiles = [];
  try {
    const parsed = typeof p.thumbnailFilesJson === "string" ? JSON.parse(p.thumbnailFilesJson) : p.thumbnailFilesJson;
    if (Array.isArray(parsed)) thumbFiles = parsed.map(normalizeThumbFileEntry).filter(Boolean);
  } catch {
    thumbFiles = [];
  }
  const primaryThumb = primaryThumbnailPath(thumbFiles);
  await pgPool.query(
    `UPDATE ai_use_case_submissions SET
      title = $1,
      situation = $2,
      task_goal = $3,
      action_taken = $4,
      result_outcome = $5,
      tags = $6,
      thumbnail_relative_path = $7,
      thumbnail_files = $8::jsonb,
      attachment_files = $9::jsonb,
      updated_at = NOW()
    WHERE id = $10`,
    [
      p.title,
      p.situation || "",
      p.taskGoal || "",
      p.actionTaken || "",
      p.resultOutcome || "",
      p.tags && p.tags.length ? p.tags : [],
      primaryThumb,
      JSON.stringify(thumbFiles),
      p.attachmentFilesJson || "[]",
      p.id,
    ]
  );
  return { ok: true };
}

const UUID_RE =
  /^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$/i;

/**
 * 상세 페이지 방문마다 view_count +1 (페이지뷰). 제출자 본인(로그인) 조회만 제외.
 * @param {import("pg").Pool} pgPool
 * @param {string} submissionId
 * @param {string | null | undefined} viewerEmail
 * @param {string | null | undefined} submitterEmail
 * @returns {Promise<number>}
 */
async function recordViewFromOther(pgPool, submissionId, viewerEmail, submitterEmail) {
  if (!pgPool || !submissionId || !UUID_RE.test(submissionId)) return 0;
  const viewer = String(viewerEmail || "")
    .trim()
    .toLowerCase();
  const submitter = String(submitterEmail || "")
    .trim()
    .toLowerCase();
  if (viewer && submitter && viewer === submitter) {
    const cur = await pgPool.query(
      `SELECT view_count FROM ai_use_case_submissions WHERE id = $1::uuid LIMIT 1`,
      [submissionId]
    );
    return cur.rows[0]?.view_count ?? 0;
  }
  await pgPool.query(
    `UPDATE ai_use_case_submissions SET view_count = view_count + 1 WHERE id = $1::uuid`,
    [submissionId]
  );
  const r = await pgPool.query(
    `SELECT view_count FROM ai_use_case_submissions WHERE id = $1::uuid LIMIT 1`,
    [submissionId]
  );
  return r.rows[0]?.view_count ?? 0;
}

/**
 * @param {import("pg").Pool} pgPool
 * @param {string} submissionId
 * @param {string | null | undefined} userEmail
 * @returns {Promise<{ viewCount: number, likeCount: number, myLike: boolean }>}
 */
async function getSubmissionEngagement(pgPool, submissionId, userEmail) {
  if (!pgPool || !submissionId || !UUID_RE.test(submissionId)) {
    return { viewCount: 0, likeCount: 0, myLike: false };
  }
  const stats = await pgPool.query(
    `SELECT
       COALESCE(s.view_count, 0)::int AS view_count,
       (SELECT COUNT(*)::int FROM ai_use_case_submission_likes l WHERE l.submission_id = s.id) AS like_count
     FROM ai_use_case_submissions s
     WHERE s.id = $1::uuid
     LIMIT 1`,
    [submissionId]
  );
  if (!stats.rowCount) {
    return { viewCount: 0, likeCount: 0, myLike: false };
  }
  const row = stats.rows[0];
  let myLike = false;
  const email = String(userEmail || "")
    .trim()
    .toLowerCase();
  if (email) {
    const mine = await pgPool.query(
      `SELECT 1 FROM ai_use_case_submission_likes
       WHERE submission_id = $1::uuid AND user_email = $2
       LIMIT 1`,
      [submissionId, email]
    );
    myLike = mine.rowCount > 0;
  }
  return {
    viewCount: row.view_count || 0,
    likeCount: row.like_count || 0,
    myLike,
  };
}

/**
 * @param {import("pg").Pool} pgPool
 * @param {string} submissionId
 * @param {string} userEmail
 * @returns {Promise<{ liked: boolean, likeCount: number }>}
 */
async function toggleSubmissionLike(pgPool, submissionId, userEmail) {
  if (!pgPool) {
    const err = new Error("DB를 사용할 수 없습니다.");
    err.code = "NO_DB";
    throw err;
  }
  const id = String(submissionId || "").trim();
  if (!UUID_RE.test(id)) {
    const err = new Error("잘못된 ID입니다.");
    err.code = "VALIDATION";
    throw err;
  }
  const email = String(userEmail || "")
    .trim()
    .toLowerCase();
  if (!email) {
    const err = new Error("로그인이 필요합니다.");
    err.code = "UNAUTHORIZED";
    throw err;
  }
  const exists = await pgPool.query(
    `SELECT 1 FROM ai_use_case_submissions WHERE id = $1::uuid LIMIT 1`,
    [id]
  );
  if (!exists.rowCount) {
    const err = new Error("사례를 찾을 수 없습니다.");
    err.code = "NOT_FOUND";
    throw err;
  }
  const del = await pgPool.query(
    `DELETE FROM ai_use_case_submission_likes
     WHERE submission_id = $1::uuid AND user_email = $2
     RETURNING submission_id`,
    [id, email]
  );
  if (del.rowCount) {
    const cnt = await pgPool.query(
      `SELECT COUNT(*)::int AS c FROM ai_use_case_submission_likes WHERE submission_id = $1::uuid`,
      [id]
    );
    return { liked: false, likeCount: cnt.rows[0]?.c || 0 };
  }
  await pgPool.query(
    `INSERT INTO ai_use_case_submission_likes (submission_id, user_email) VALUES ($1::uuid, $2)`,
    [id, email]
  );
  const cnt = await pgPool.query(
    `SELECT COUNT(*)::int AS c FROM ai_use_case_submission_likes WHERE submission_id = $1::uuid`,
    [id]
  );
  return { liked: true, likeCount: cnt.rows[0]?.c || 0 };
}

module.exports = {
  PLACEHOLDERS,
  MAX_BODY_TOTAL,
  MAX_THUMB_BYTES,
  MAX_THUMB_COUNT,
  MAX_ATTACH_BYTES,
  MAX_ATTACH_COUNT,
  countBodyTotal,
  normalizeThumbFileEntry,
  parseThumbnailFiles,
  primaryThumbnailPath,
  insertSubmission,
  buildExcerpt,
  mapRowToListCard,
  listForPublicList,
  getSubmissionById,
  canUserEditSubmission,
  buildComposeEditorHtml,
  updateSubmission,
  recordViewFromOther,
  getSubmissionEngagement,
  toggleSubmissionLike,
  SUBMISSION_DEPT,
};