개인정보-컴플라이언스-웹애플리케이션 (수정사항 7)

현재 테이블 구조가 정량 테이블, 정성 테이블 에 피드백이 달려있는 구조였습니다.

하지만 그렇게 할 경우 피드백 히스토리를 볼 수 없을 뿐만 아니라 여러 관리자가 피드백을 작성할시 중복으로 저장하기 어렵다고 생각이 들어서 

-- 정량 문항 테이블
CREATE TABLE quantitative_questions (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '문항 ID',
    question_number INT NOT NULL COMMENT '문항 번호',
    question TEXT NOT NULL COMMENT '문항 내용',
    evaluation_criteria TEXT COMMENT '평가기준',
    legal_basis TEXT COMMENT '근거 법령',
    score DECIMAL(5,2) DEFAULT NULL COMMENT '배점',
    UNIQUE KEY uk_question_number (question_number)
)

-- 정량 응답 테이블 (quantitative_responses)
CREATE TABLE quantitative_responses (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '응답 ID',
    systems_id INT NOT NULL COMMENT '시스템 ID',
    user_id INT NOT NULL COMMENT '회원 ID',
    question_id INT NOT NULL COMMENT '문항 ID',
    response ENUM('이행', '미이행', '해당없음', '자문필요') DEFAULT NULL COMMENT '응답',
    additional_comment TEXT COMMENT '추가 의견',
    file_path VARCHAR(255) DEFAULT NULL COMMENT '파일 업로드 경로',
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '마지막 수정 시간',
    
    -- UNIQUE 제약 조건 추가
    CONSTRAINT uk_system_user_question UNIQUE (systems_id, user_id, question_id),
    
    -- FOREIGN KEY 설정
    CONSTRAINT fk_quantitative_responses_system FOREIGN KEY (systems_id) REFERENCES systems(id) ON DELETE CASCADE,
    CONSTRAINT fk_quantitative_responses_user FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE,
    CONSTRAINT fk_quantitative_responses_question FOREIGN KEY (question_id) REFERENCES quantitative_questions(id) ON DELETE CASCADE
);

 

-- 정성 문항 테이블
CREATE TABLE qualitative_questions (
    id INT NOT NULL AUTO_INCREMENT COMMENT '문항 ID',
    question_number INT NOT NULL COMMENT '문항 번호',
    indicator TEXT NOT NULL COMMENT '지표',
    indicator_definition TEXT COMMENT '지표 정의',
    evaluation_criteria TEXT COMMENT '평가기준',
    reference_info TEXT COMMENT '참고사항',
    PRIMARY KEY (id),
    UNIQUE KEY uk_question_number (question_number)
)


-- 정성 응답 테이블 (qualitative_responses)
CREATE TABLE qualitative_responses (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '응답 ID',
    systems_id INT NOT NULL COMMENT '시스템 ID',
    user_id INT NOT NULL COMMENT '회원 ID',
    question_id INT NOT NULL COMMENT '문항 ID',
    response ENUM('자문필요', '해당없음') DEFAULT NULL COMMENT '응답 상태',
    additional_comment TEXT COMMENT '추가 의견',
    file_path VARCHAR(255) DEFAULT NULL COMMENT '파일 업로드 경로',
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '마지막 수정 시간',
    
    -- UNIQUE 제약 조건 추가
    CONSTRAINT uk_system_user_question UNIQUE (systems_id, user_id, question_id),
    
    -- FOREIGN KEY 설정
    CONSTRAINT fk_qualitative_responses_system FOREIGN KEY (systems_id) REFERENCES systems(id) ON DELETE CASCADE,
    CONSTRAINT fk_qualitative_responses_user FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE,
    CONSTRAINT fk_qualitative_responses_question FOREIGN KEY (question_id) REFERENCES qualitative_questions(id) ON DELETE CASCADE
);

 

(정량 문항, 정성 응답) 과 (정성 문항, 정성 응답) 으로 테이블 구조를 나눴고 피드백 테이블을 따로 구축하였습니다.

CREATE TABLE feedback (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '피드백 ID',
    systems_id INT NOT NULL COMMENT '시스템 ID',
    user_id INT NOT NULL COMMENT '기관회원 ID',
    expert_id INT NOT NULL COMMENT '전문가 ID',
    quantitative_response_id INT NULL COMMENT '정량 응답 ID (quantitative_responses)',
    qualitative_response_id INT NULL COMMENT '정성 응답 ID (qualitative_responses)',
    feedback TEXT NOT NULL COMMENT '피드백 내용',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '피드백 생성 날짜',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '피드백 수정 날짜',

    -- ✅ 관계 설정 (정량/정성 응답 테이블을 각각 참조)
    FOREIGN KEY (system_id) REFERENCES systems(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES User(id) ON DELETE CASCADE,
    FOREIGN KEY (expert_id) REFERENCES Expert(id) ON DELETE CASCADE,
    FOREIGN KEY (quantitative_response_id) REFERENCES quantitative_responses(id) ON DELETE CASCADE,
    FOREIGN KEY (qualitative_response_id) REFERENCES qualitative_responses(id) ON DELETE CASCADE
);

 

이런식으로 구성을 하면 피드백의 히스토리를 볼수 있고 여러 관리자(전문가)의 피드백을 볼수 있게 되면서 더욱 좋은 테이블 구조를 가지게 되었습니다.

 

정량 피드백 제출

submitQuantitativeFeedback 함수

const submitQuantitativeFeedback = async (req, res) => {
  const { systemId, expertId, feedbackResponses } = req.body;

  if (!systemId || !expertId || !Array.isArray(feedbackResponses)) {
    return res.status(400).json({
      resultCode: "F-1",
      msg: "잘못된 요청 형식입니다.",
    });
  }

  try {
    const connection = await pool.getConnection();
    await connection.beginTransaction();

    for (const { questionNumber, feedback } of feedbackResponses) {
      await connection.query(
        `INSERT INTO feedback (systems_id, user_id, expert_id, quantitative_response_id, feedback, created_at)
         VALUES (?, ?, ?, 
           (SELECT id FROM quantitative_responses WHERE systems_id = ? AND question_id = ? LIMIT 1),
           ?, NOW())`,
        [systemId, expertId, expertId, systemId, questionNumber, feedback]
      );
    }

    await connection.commit();
    connection.release();

    res.status(200).json({ resultCode: "S-1", msg: "피드백 저장 완료" });
  } catch (error) {
    res.status(500).json({ resultCode: "F-1", msg: "서버 오류 발생", error: error.message });
  }
};

전문가가 정량 피드백을 제출하는 API.
quantitative_responses 테이블에서 해당 시스템의 특정 문항 ID를 찾고, 이를 feedback 테이블에 저장합니다.
transaction을 사용하여 여러 개의 피드백을 한 번에 저장하며, 중간에 실패하면 롤백하여 데이터 정합성을 유지합니다.

 

정성 피드백 제출

submitQualitativeFeedback 함수

const submitQualitativeFeedback = async (req, res) => {
  const { systemId, expertId, feedbackResponses } = req.body;

  if (!systemId || !expertId || !Array.isArray(feedbackResponses)) {
    return res.status(400).json({
      resultCode: "F-1",
      msg: "잘못된 요청 형식입니다.",
    });
  }

  try {
    const connection = await pool.getConnection();
    await connection.beginTransaction();

    for (const { questionNumber, feedback } of feedbackResponses) {
      const [responseResult] = await connection.query(
        `SELECT id FROM qualitative_responses 
         WHERE systems_id = ? AND question_id = ? 
         ORDER BY updated_at DESC LIMIT 1`,
        [systemId, questionNumber]
      );

      if (responseResult.length === 0) {
        continue;
      }

      const qualitativeResponseId = responseResult[0].id;

      await connection.query(
        `INSERT INTO feedback (systems_id, user_id, expert_id, qualitative_response_id, feedback, created_at)
         VALUES (?, ?, ?, ?, ?, NOW())`,
        [systemId, expertId, expertId, qualitativeResponseId, feedback]
      );
    }

    await connection.commit();
    connection.release();

    res.status(200).json({
      resultCode: "S-1",
      msg: "정성 피드백 저장 완료.",
    });
  } catch (error) {
    res.status(500).json({
      resultCode: "F-1",
      msg: "서버 오류 발생",
      error: error.message,
    });
  }
};

 

정성 피드백을 저장하는 API.
qualitative_responses 테이블에서 가장 최근 응답을 찾아 해당 id를 feedback 테이블에 저장합니다.

 

 

피드백 조회

getFeedbacks 함수

const getFeedbacks = async (req, res) => {
  const { systemId, questionNumber } = req.query;

  if (!systemId) {
    return res.status(400).json({
      resultCode: "F-1",
      msg: "System ID가 필요합니다.",
    });
  }

  try {
    const query = `
      SELECT f.id AS feedback_id, f.feedback, f.created_at, 
             qr.question_id AS quantitative_question_id,
             qlr.question_id AS qualitative_question_id,
             e.name AS expert_name
      FROM feedback f
      JOIN expert e ON f.expert_id = e.id
      LEFT JOIN quantitative_responses qr ON f.quantitative_response_id = qr.id
      LEFT JOIN qualitative_responses qlr ON f.qualitative_response_id = qlr.id
      WHERE f.systems_id = ? 
      ORDER BY f.created_at DESC;
    `;

    const [results] = await pool.query(query, [systemId]);

    res.status(200).json({
      resultCode: "S-1",
      msg: "피드백 조회 성공",
      data: results,
    });
  } catch (error) {
    res.status(500).json({
      resultCode: "F-1",
      msg: "서버 오류 발생",
      error: error.message,
    });
  }
};

 

 

정량 문항 및 응답 관리

submitQuantitativeResponses 함수

const submitQuantitativeResponses = async (req, res) => {
  const { responses } = req.body;
  const user_id = req.session.user?.id;

  if (!user_id) {
    return res.status(401).json({ message: "로그인이 필요합니다." });
  }

  if (!responses || !Array.isArray(responses)) {
    return res.status(400).json({ message: "Invalid responses format." });
  }

  try {
    const query = `
      INSERT INTO quantitative_responses (systems_id, user_id, question_id, response, additional_comment, file_path)
      VALUES (?, ?, ?, ?, ?, ?)
      ON DUPLICATE KEY UPDATE 
        response = VALUES(response), 
        additional_comment = VALUES(additional_comment), 
        file_path = VALUES(file_path);
    `;

    const connection = await pool.getConnection();
    await connection.beginTransaction();

    for (const {
      systemId,
      questionId,
      response,
      additionalComment,
      filePath,
    } of responses) {
      const normalizedResponse =
        response && response.trim() ? response.trim() : "이행";
      const safeAdditionalComment =
        normalizedResponse === "자문필요"
          ? additionalComment?.trim() || "자문 요청"
          : "";

      await connection.query(query, [
        systemId,
        user_id,
        questionId,
        normalizedResponse,
        safeAdditionalComment,
        filePath || null,
      ]);
    }

    await connection.commit();
    connection.release();

    res.status(200).json({ message: "정량 응답 저장 완료" });
  } catch (error) {
    res.status(500).json({ message: "서버 오류 발생", error: error.message });
  }
};

 

사용자가 정량 평가 문항에 대한 응답을 제출하는 API.
응답을 저장하면서 response 값이 "자문필요"이면 additional_comment를 저장합니다.
기존 응답이 있으면 ON DUPLICATE KEY UPDATE를 사용하여 업데이트합니다.

 

 

정성 문항 및 응답 관리

submitQualitativeResponses 함수

const submitQualitativeResponses = async (req, res) => {
  const { responses } = req.body;
  const user_id = req.session.user?.id;

  if (!user_id) {
    return res.status(401).json({ message: "로그인이 필요합니다." });
  }

  if (!responses || !Array.isArray(responses)) {
    return res.status(400).json({ message: "Invalid responses format." });
  }

  try {
    const query = `
      INSERT INTO qualitative_responses 
      (systems_id, user_id, question_id, response, additional_comment, file_path)
      VALUES (?, ?, ?, ?, ?, ?)
      ON DUPLICATE KEY UPDATE 
        response = VALUES(response), 
        additional_comment = CASE 
          WHEN VALUES(response) = '자문필요' THEN VALUES(additional_comment) 
          ELSE NULL 
        END,
        file_path = VALUES(file_path);
    `;

    const connection = await pool.getConnection();
    await connection.beginTransaction();

    for (const {
      systemId,
      questionId,
      response,
      additionalComment,
      filePath,
    } of responses) {
      const normalizedResponse = response.replace(/\s+/g, "");
      if (!["자문필요", "해당없음"].includes(normalizedResponse)) {
        throw new Error(`Invalid response value: ${response}`);
      }

      const safeAdditionalComment =
        normalizedResponse === "자문필요"
          ? additionalComment?.trim() || "자문요청"
          : null;

      await connection.query(query, [
        systemId,
        user_id,
        questionId,
        normalizedResponse,
        safeAdditionalComment,
        filePath || null,
      ]);
    }

    await connection.commit();
    connection.release();

    res.status(200).json({ message: "정성 응답 저장 완료" });
  } catch (error) {
    res.status(500).json({ message: "서버 오류 발생", error: error.message });
  }
};

 

사용자가 정성 평가 문항에 대한 응답을 제출하는 API.
응답 값(response)이 **"자문필요"**이면 additional_comment를 저장합니다.
ON DUPLICATE KEY UPDATE를 사용하여 기존 데이터 업데이트합니다.

 

 

문항 수정

정량 문항 업데이트

const updateQuantitativeQuestion = async (req, res) => {
  const { questionId, question, evaluationCriteria, legalBasis, score } =
    req.body;

  if (!questionId || !question || !evaluationCriteria || !score) {
    return res
      .status(400)
      .json({ message: "필수 입력 항목이 누락되었습니다." });
  }

  try {
    const query = `
      UPDATE quantitative_questions
      SET question = ?, evaluation_criteria = ?, legal_basis = ?, score = ?
      WHERE id = ?;
    `;

    const [result] = await pool.query(query, [
      question,
      evaluationCriteria,
      legalBasis || null,
      score,
      questionId,
    ]);

    if (result.affectedRows === 0) {
      return res.status(404).json({
        message: "해당 정량 문항을 찾을 수 없습니다.",
      });
    }

    res.status(200).json({ message: "정량 문항 업데이트 성공" });
  } catch (error) {
    res.status(500).json({ message: "서버 오류 발생", error: error.message });
  }
};

 

정량 문항을 업데이트하는 API.
questionId 기준으로 기존 데이터를 업데이트합니다.

 

 

 

문항 수정

정성 문항 업데이트

const updateQualitativeQuestion = async (req, res) => {
  const {
    questionId,
    indicator,
    indicatorDefinition,
    evaluationCriteria,
    referenceInfo,
  } = req.body;

  if (!questionId || !indicator || !evaluationCriteria) {
    return res
      .status(400)
      .json({ message: "필수 입력 항목이 누락되었습니다." });
  }

  try {
    const query = `
      UPDATE qualitative_questions
      SET indicator = ?, indicator_definition = ?, evaluation_criteria = ?, reference_info = ?
      WHERE id = ?;
    `;

    const [result] = await pool.query(query, [
      indicator,
      indicatorDefinition || null,
      evaluationCriteria,
      referenceInfo || null,
      questionId,
    ]);

    if (result.affectedRows === 0) {
      return res.status(404).json({
        message: "해당 정성 문항을 찾을 수 없습니다.",
      });
    }

    res.status(200).json({ message: "정성 문항 업데이트 성공" });
  } catch (error) {
    console.error("❌ [ERROR] 정성 문항 업데이트 실패:", error.message);
    res.status(500).json({ message: "서버 오류 발생", error: error.message });
  }
};

 

정성 문항을 업데이트하는 API.
questionId 기준으로 기존 데이터를 업데이트합니다.