tk-factory-services/tksafety/api/models/riskModel.js

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

// ==================== DB 마이그레이션 ====================

const runMigration = async () => {
  const db = getPool();
  try {
    // 1. risk_process_templates (마스터)
    await db.query(`
      CREATE TABLE IF NOT EXISTS risk_process_templates (
        id INT AUTO_INCREMENT PRIMARY KEY,
        product_type ENUM('PKG','VESSEL','HX','SKID') NOT NULL,
        process_name VARCHAR(100) NOT NULL,
        display_order INT NOT NULL DEFAULT 0,
        UNIQUE KEY uk_type_name (product_type, process_name)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
    `);

    // 2. risk_projects (평가 프로젝트)
    await db.query(`
      CREATE TABLE IF NOT EXISTS risk_projects (
        id INT AUTO_INCREMENT PRIMARY KEY,
        title VARCHAR(200) NOT NULL,
        assessment_type ENUM('regular','adhoc') NOT NULL DEFAULT 'regular',
        product_type ENUM('PKG','VESSEL','HX','SKID') NOT NULL,
        year SMALLINT NOT NULL,
        month TINYINT NULL,
        status ENUM('draft','in_progress','completed') NOT NULL DEFAULT 'draft',
        assessed_by VARCHAR(100) NULL,
        created_by INT NULL,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_type_year (assessment_type, year),
        INDEX idx_product (product_type),
        INDEX idx_status (status)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
    `);

    // 3. risk_processes (세부 공정)
    await db.query(`
      CREATE TABLE IF NOT EXISTS risk_processes (
        id INT AUTO_INCREMENT PRIMARY KEY,
        project_id INT NOT NULL,
        process_name VARCHAR(100) NOT NULL,
        display_order INT NOT NULL DEFAULT 0,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_project (project_id),
        FOREIGN KEY (project_id) REFERENCES risk_projects(id) ON DELETE CASCADE
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
    `);

    // 4. risk_assessment_items (평가 항목)
    await db.query(`
      CREATE TABLE IF NOT EXISTS risk_assessment_items (
        id INT AUTO_INCREMENT PRIMARY KEY,
        process_id INT NOT NULL,
        category VARCHAR(100) NULL,
        cause VARCHAR(200) NULL,
        hazard TEXT NULL,
        regulation VARCHAR(200) NULL,
        current_measure TEXT NULL,
        likelihood TINYINT NULL,
        severity TINYINT NULL,
        risk_score TINYINT GENERATED ALWAYS AS (likelihood * severity) STORED,
        mitigation_no VARCHAR(20) NULL,
        detail TEXT NULL,
        display_order INT NOT NULL DEFAULT 0,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_process (process_id),
        FOREIGN KEY (process_id) REFERENCES risk_processes(id) ON DELETE CASCADE
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
    `);

    // 5. risk_mitigations (감소대책)
    await db.query(`
      CREATE TABLE IF NOT EXISTS risk_mitigations (
        id INT AUTO_INCREMENT PRIMARY KEY,
        project_id INT NOT NULL,
        mitigation_no VARCHAR(20) NOT NULL,
        hazard_summary TEXT NULL,
        current_risk_score TINYINT NULL,
        improvement_plan TEXT NULL,
        manager VARCHAR(100) NULL,
        budget VARCHAR(100) NULL,
        schedule VARCHAR(100) NULL,
        completion_photo VARCHAR(500) NULL,
        completion_date DATE NULL,
        post_likelihood TINYINT NULL,
        post_severity TINYINT NULL,
        post_risk_score TINYINT GENERATED ALWAYS AS (post_likelihood * post_severity) STORED,
        status ENUM('planned','in_progress','completed') NOT NULL DEFAULT 'planned',
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_project (project_id),
        UNIQUE KEY uk_proj_no (project_id, mitigation_no),
        FOREIGN KEY (project_id) REFERENCES risk_projects(id) ON DELETE CASCADE
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
    `);

    // 마스터 시딩 (INSERT IGNORE — 중복 무시)
    const templates = [
      ['PKG', '가공', 1], ['PKG', '용접', 2], ['PKG', '사상', 3],
      ['PKG', '조립(피팅)', 4], ['PKG', '도장', 5], ['PKG', '검사', 6],
      ['VESSEL', '절단', 1], ['VESSEL', '가공', 2], ['VESSEL', '조립', 3],
      ['VESSEL', '용접', 4], ['VESSEL', '검사', 5], ['VESSEL', '열처리', 6],
      ['VESSEL', '사상', 7], ['VESSEL', '도장', 8],
      ['HX', '절단', 1], ['HX', '가공', 2], ['HX', '조립', 3],
      ['HX', '용접', 4], ['HX', '확관', 5], ['HX', '사상/도장', 6],
      ['HX', '검사', 7],
      ['SKID', '절단', 1], ['SKID', '가공', 2], ['SKID', '배관조립', 3],
      ['SKID', '용접', 4], ['SKID', '계장설치', 5], ['SKID', '사상/도장', 6],
      ['SKID', '검사', 7],
    ];
    for (const [productType, processName, order] of templates) {
      await db.query(
        'INSERT IGNORE INTO risk_process_templates (product_type, process_name, display_order) VALUES (?, ?, ?)',
        [productType, processName, order]
      );
    }

    console.log('[migration] risk tables + templates ready');
  } catch (err) {
    console.error('[migration] Risk migration error:', err.message);
  }
};

// ==================== 공정 템플릿 조회 ====================

const getTemplates = async (productType) => {
  const db = getPool();
  let query = 'SELECT * FROM risk_process_templates';
  const params = [];
  if (productType) {
    query += ' WHERE product_type = ?';
    params.push(productType);
  }
  query += ' ORDER BY product_type, display_order';
  const [rows] = await db.query(query, params);
  return rows;
};

// ==================== 프로젝트 CRUD ====================

const createProject = async (data) => {
  const db = getPool();
  const connection = await db.getConnection();
  try {
    await connection.beginTransaction();

    const { title, assessment_type = 'regular', product_type, year, month = null, assessed_by = null, created_by = null } = data;

    const [result] = await connection.query(
      `INSERT INTO risk_projects (title, assessment_type, product_type, year, month, assessed_by, created_by)
       VALUES (?, ?, ?, ?, ?, ?, ?)`,
      [title, assessment_type, product_type, year, month, assessed_by, created_by]
    );
    const projectId = result.insertId;

    // 정기 평가: 템플릿에서 세부 공정 자동 생성
    if (assessment_type === 'regular') {
      const [templates] = await connection.query(
        'SELECT process_name, display_order FROM risk_process_templates WHERE product_type = ? ORDER BY display_order',
        [product_type]
      );
      for (const t of templates) {
        await connection.query(
          'INSERT INTO risk_processes (project_id, process_name, display_order) VALUES (?, ?, ?)',
          [projectId, t.process_name, t.display_order]
        );
      }
    }

    await connection.commit();
    return projectId;
  } catch (err) {
    await connection.rollback();
    throw err;
  } finally {
    connection.release();
  }
};

const getAllProjects = async (filters = {}) => {
  const db = getPool();
  let query = `
    SELECT p.*,
      (SELECT COUNT(*) FROM risk_assessment_items i
       JOIN risk_processes rp ON i.process_id = rp.id
       WHERE rp.project_id = p.id) AS total_items,
      (SELECT COUNT(*) FROM risk_assessment_items i
       JOIN risk_processes rp ON i.process_id = rp.id
       WHERE rp.project_id = p.id AND i.risk_score >= 16) AS high_risk_count,
      (SELECT COUNT(*) FROM risk_mitigations m
       WHERE m.project_id = p.id AND m.status != 'completed') AS open_mitigations
    FROM risk_projects p
    WHERE 1=1
  `;
  const params = [];

  if (filters.assessment_type) {
    query += ' AND p.assessment_type = ?';
    params.push(filters.assessment_type);
  }
  if (filters.year) {
    query += ' AND p.year = ?';
    params.push(filters.year);
  }
  if (filters.product_type) {
    query += ' AND p.product_type = ?';
    params.push(filters.product_type);
  }
  if (filters.status) {
    query += ' AND p.status = ?';
    params.push(filters.status);
  }

  query += ' ORDER BY p.year DESC, p.created_at DESC';

  const [rows] = await db.query(query, params);
  return rows;
};

const getProjectById = async (id) => {
  const db = getPool();

  const [projects] = await db.query('SELECT * FROM risk_projects WHERE id = ?', [id]);
  if (!projects.length) return null;
  const project = projects[0];

  // 세부 공정 + 항목 (3단계 중첩)
  const [processes] = await db.query(
    'SELECT * FROM risk_processes WHERE project_id = ? ORDER BY display_order', [id]
  );
  for (const proc of processes) {
    const [items] = await db.query(
      'SELECT * FROM risk_assessment_items WHERE process_id = ? ORDER BY display_order, id', [proc.id]
    );
    proc.items = items;
  }
  project.processes = processes;

  // 감소대책
  const [mitigations] = await db.query(
    'SELECT * FROM risk_mitigations WHERE project_id = ? ORDER BY CAST(mitigation_no AS UNSIGNED), mitigation_no', [id]
  );
  project.mitigations = mitigations;

  return project;
};

const updateProject = async (id, data) => {
  const db = getPool();
  const fields = [];
  const params = [];

  for (const key of ['title', 'assessment_type', 'product_type', 'year', 'month', 'status', 'assessed_by']) {
    if (data[key] !== undefined) {
      fields.push(`${key} = ?`);
      params.push(data[key]);
    }
  }
  if (!fields.length) return { affectedRows: 0 };

  params.push(id);
  const [result] = await db.query(
    `UPDATE risk_projects SET ${fields.join(', ')} WHERE id = ?`, params
  );
  return result;
};

const deleteProject = async (id) => {
  const db = getPool();
  const [result] = await db.query('DELETE FROM risk_projects WHERE id = ?', [id]);
  return result;
};

// ==================== 세부 공정 ====================

const addProcess = async (projectId, data) => {
  const db = getPool();
  const { process_name, display_order = 0 } = data;
  const [result] = await db.query(
    'INSERT INTO risk_processes (project_id, process_name, display_order) VALUES (?, ?, ?)',
    [projectId, process_name, display_order]
  );
  return result.insertId;
};

// ==================== 평가 항목 CRUD ====================

const createItem = async (processId, data) => {
  const db = getPool();
  const { category = null, cause = null, hazard = null, regulation = null,
          current_measure = null, likelihood = null, severity = null,
          mitigation_no = null, detail = null, display_order = 0 } = data;
  const [result] = await db.query(
    `INSERT INTO risk_assessment_items
     (process_id, category, cause, hazard, regulation, current_measure,
      likelihood, severity, mitigation_no, detail, display_order)
     VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
    [processId, category, cause, hazard, regulation, current_measure,
     likelihood, severity, mitigation_no, detail, display_order]
  );
  return result.insertId;
};

const updateItem = async (itemId, data) => {
  const db = getPool();
  const fields = [];
  const params = [];

  for (const key of ['category', 'cause', 'hazard', 'regulation', 'current_measure',
                      'likelihood', 'severity', 'mitigation_no', 'detail', 'display_order']) {
    if (data[key] !== undefined) {
      fields.push(`${key} = ?`);
      params.push(data[key]);
    }
  }
  if (!fields.length) return { affectedRows: 0 };

  params.push(itemId);
  const [result] = await db.query(
    `UPDATE risk_assessment_items SET ${fields.join(', ')} WHERE id = ?`, params
  );
  return result;
};

const deleteItem = async (itemId) => {
  const db = getPool();
  const [result] = await db.query('DELETE FROM risk_assessment_items WHERE id = ?', [itemId]);
  return result;
};

// ==================== 감소대책 CRUD ====================

const getMitigationsByProject = async (projectId) => {
  const db = getPool();
  const [rows] = await db.query(
    'SELECT * FROM risk_mitigations WHERE project_id = ? ORDER BY CAST(mitigation_no AS UNSIGNED), mitigation_no',
    [projectId]
  );
  return rows;
};

const createMitigation = async (projectId, data) => {
  const db = getPool();
  const { mitigation_no, hazard_summary = null, current_risk_score = null,
          improvement_plan = null, manager = null, budget = null, schedule = null } = data;
  const [result] = await db.query(
    `INSERT INTO risk_mitigations
     (project_id, mitigation_no, hazard_summary, current_risk_score, improvement_plan, manager, budget, schedule)
     VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
    [projectId, mitigation_no, hazard_summary, current_risk_score, improvement_plan, manager, budget, schedule]
  );
  return result.insertId;
};

const updateMitigation = async (mitigationId, data) => {
  const db = getPool();
  const fields = [];
  const params = [];

  for (const key of ['mitigation_no', 'hazard_summary', 'current_risk_score', 'improvement_plan',
                      'manager', 'budget', 'schedule', 'completion_photo', 'completion_date',
                      'post_likelihood', 'post_severity', 'status']) {
    if (data[key] !== undefined) {
      fields.push(`${key} = ?`);
      params.push(data[key]);
    }
  }
  if (!fields.length) return { affectedRows: 0 };

  params.push(mitigationId);
  const [result] = await db.query(
    `UPDATE risk_mitigations SET ${fields.join(', ')} WHERE id = ?`, params
  );
  return result;
};

const updateMitigationPhoto = async (mitigationId, photoPath) => {
  const db = getPool();
  const [result] = await db.query(
    'UPDATE risk_mitigations SET completion_photo = ? WHERE id = ?',
    [photoPath, mitigationId]
  );
  return result;
};

module.exports = {
  runMigration,
  getTemplates,
  createProject,
  getAllProjects,
  getProjectById,
  updateProject,
  deleteProject,
  addProcess,
  createItem,
  updateItem,
  deleteItem,
  getMitigationsByProject,
  createMitigation,
  updateMitigation,
  updateMitigationPhoto
};