tk-factory-services/user-management/api/models/taskModel.js

/**
 * Task Model
 *
 * work_types + tasks 테이블 CRUD (MariaDB)
 * System 1과 같은 DB를 공유
 */

const { getPool } = require('./userModel');

/* ===== Work Types (공정) ===== */

async function getWorkTypes() {
  const db = getPool();
  const [rows] = await db.query(
    'SELECT * FROM work_types ORDER BY category ASC, name ASC'
  );
  return rows;
}

async function getWorkTypeById(id) {
  const db = getPool();
  const [rows] = await db.query('SELECT * FROM work_types WHERE id = ?', [id]);
  return rows[0] || null;
}

async function createWorkType({ name, category, description }) {
  const db = getPool();
  const [result] = await db.query(
    'INSERT INTO work_types (name, category, description) VALUES (?, ?, ?)',
    [name, category || null, description || null]
  );
  return getWorkTypeById(result.insertId);
}

async function updateWorkType(id, data) {
  const db = getPool();
  const fields = [];
  const values = [];
  if (data.name !== undefined) { fields.push('name = ?'); values.push(data.name); }
  if (data.category !== undefined) { fields.push('category = ?'); values.push(data.category || null); }
  if (data.description !== undefined) { fields.push('description = ?'); values.push(data.description || null); }
  if (fields.length === 0) return getWorkTypeById(id);
  values.push(id);
  await db.query(`UPDATE work_types SET ${fields.join(', ')} WHERE id = ?`, values);
  return getWorkTypeById(id);
}

async function deleteWorkType(id) {
  const db = getPool();
  // 연결된 tasks의 work_type_id를 NULL로 설정 (FK cascade가 처리하지만 명시적으로)
  await db.query('UPDATE tasks SET work_type_id = NULL WHERE work_type_id = ?', [id]);
  await db.query('DELETE FROM work_types WHERE id = ?', [id]);
}

/* ===== Tasks (작업) ===== */

async function getTasks(workTypeId) {
  const db = getPool();
  let sql = `SELECT t.*, wt.name AS work_type_name, wt.category AS work_type_category
             FROM tasks t
             LEFT JOIN work_types wt ON t.work_type_id = wt.id`;
  const params = [];
  if (workTypeId) {
    sql += ' WHERE t.work_type_id = ?';
    params.push(workTypeId);
  }
  sql += ' ORDER BY wt.category ASC, t.task_id DESC';
  const [rows] = await db.query(sql, params);
  return rows;
}

async function getActiveTasks() {
  const db = getPool();
  const [rows] = await db.query(
    `SELECT t.*, wt.name AS work_type_name, wt.category AS work_type_category
     FROM tasks t
     LEFT JOIN work_types wt ON t.work_type_id = wt.id
     WHERE t.is_active = TRUE
     ORDER BY wt.category ASC, t.task_name ASC`
  );
  return rows;
}

async function getTaskById(id) {
  const db = getPool();
  const [rows] = await db.query(
    `SELECT t.*, wt.name AS work_type_name, wt.category AS work_type_category
     FROM tasks t
     LEFT JOIN work_types wt ON t.work_type_id = wt.id
     WHERE t.task_id = ?`,
    [id]
  );
  return rows[0] || null;
}

async function createTask({ work_type_id, task_name, description, is_active }) {
  const db = getPool();
  const [result] = await db.query(
    'INSERT INTO tasks (work_type_id, task_name, description, is_active) VALUES (?, ?, ?, ?)',
    [work_type_id || null, task_name, description || null, is_active !== undefined ? is_active : true]
  );
  return getTaskById(result.insertId);
}

async function updateTask(id, data) {
  const db = getPool();
  const fields = [];
  const values = [];
  if (data.work_type_id !== undefined) { fields.push('work_type_id = ?'); values.push(data.work_type_id || null); }
  if (data.task_name !== undefined) { fields.push('task_name = ?'); values.push(data.task_name); }
  if (data.description !== undefined) { fields.push('description = ?'); values.push(data.description || null); }
  if (data.is_active !== undefined) { fields.push('is_active = ?'); values.push(data.is_active); }
  if (fields.length === 0) return getTaskById(id);
  values.push(id);
  await db.query(`UPDATE tasks SET ${fields.join(', ')} WHERE task_id = ?`, values);
  return getTaskById(id);
}

async function deleteTask(id) {
  const db = getPool();
  await db.query('DELETE FROM tasks WHERE task_id = ?', [id]);
}

module.exports = {
  getWorkTypes, getWorkTypeById, createWorkType, updateWorkType, deleteWorkType,
  getTasks, getActiveTasks, getTaskById, createTask, updateTask, deleteTask
};