tk-factory-services/system1-factory/api/models/scheduleModel.js

// models/scheduleModel.js
const { getDb } = require('../dbPool');

const ScheduleModel = {
  // === 공정 단계 ===
  async getPhases() {
    const db = await getDb();
    const [rows] = await db.query(
      'SELECT * FROM schedule_phases WHERE is_active = 1 ORDER BY display_order'
    );
    return rows;
  },

  async createPhase(data) {
    const db = await getDb();
    const [result] = await db.query(
      'INSERT INTO schedule_phases (phase_name, display_order, color) VALUES (?, ?, ?)',
      [data.phase_name, data.display_order || 0, data.color || '#3B82F6']
    );
    return result.insertId;
  },

  async updatePhase(phaseId, data) {
    const db = await getDb();
    const fields = [];
    const params = [];
    if (data.phase_name !== undefined) { fields.push('phase_name = ?'); params.push(data.phase_name); }
    if (data.display_order !== undefined) { fields.push('display_order = ?'); params.push(data.display_order); }
    if (data.color !== undefined) { fields.push('color = ?'); params.push(data.color); }
    if (data.is_active !== undefined) { fields.push('is_active = ?'); params.push(data.is_active); }
    if (fields.length === 0) return;
    params.push(phaseId);
    await db.query(`UPDATE schedule_phases SET ${fields.join(', ')} WHERE phase_id = ?`, params);
  },

  // === 작업 템플릿 ===
  async getTemplates(phaseId) {
    const db = await getDb();
    let sql = 'SELECT t.*, p.phase_name FROM schedule_task_templates t JOIN schedule_phases p ON t.phase_id = p.phase_id WHERE t.is_active = 1';
    const params = [];
    if (phaseId) { sql += ' AND t.phase_id = ?'; params.push(phaseId); }
    sql += ' ORDER BY p.display_order, t.display_order';
    const [rows] = await db.query(sql, params);
    return rows;
  },

  // === 공정표 항목 ===
  async getEntries(filters = {}) {
    const db = await getDb();
    let sql = `
      SELECT e.*, p.phase_name, p.color AS phase_color, pr.project_name, pr.job_no AS project_code,
             su.name AS created_by_name, wt.name AS work_type_name
      FROM schedule_entries e
      JOIN schedule_phases p ON e.phase_id = p.phase_id
      JOIN projects pr ON e.project_id = pr.project_id
      LEFT JOIN sso_users su ON e.created_by = su.user_id
      LEFT JOIN work_types wt ON e.work_type_id = wt.id
      WHERE 1=1
    `;
    const params = [];
    if (filters.project_id) { sql += ' AND e.project_id = ?'; params.push(filters.project_id); }
    if (filters.year) {
      sql += ' AND (YEAR(e.start_date) = ? OR YEAR(e.end_date) = ?)';
      params.push(filters.year, filters.year);
    }
    if (filters.month && filters.year) {
      sql += ' AND ((YEAR(e.start_date) = ? AND MONTH(e.start_date) = ?) OR (YEAR(e.end_date) = ? AND MONTH(e.end_date) = ?))';
      params.push(filters.year, filters.month, filters.year, filters.month);
    }
    sql += ' ORDER BY pr.job_no, p.display_order, e.display_order';
    const [rows] = await db.query(sql, params);
    return rows;
  },

  async getGanttData(year) {
    const db = await getDb();
    // 해당 연도에 걸치는 모든 항목
    const [entries] = await db.query(`
      SELECT e.*, p.phase_name, p.color AS phase_color, p.display_order AS phase_order,
             pr.project_name, pr.job_no AS project_code
      FROM schedule_entries e
      JOIN schedule_phases p ON e.phase_id = p.phase_id
      JOIN projects pr ON e.project_id = pr.project_id
      WHERE e.start_date IS NOT NULL AND e.end_date IS NOT NULL
        AND (YEAR(e.start_date) <= ? AND YEAR(e.end_date) >= ?)
        AND e.status != 'cancelled'
      ORDER BY pr.job_no, p.display_order, e.display_order
    `, [year, year]);

    // 의존관계
    const entryIds = entries.map(e => e.entry_id);
    let dependencies = [];
    if (entryIds.length > 0) {
      const [deps] = await db.query(
        `SELECT * FROM schedule_entry_dependencies WHERE entry_id IN (?)`,
        [entryIds]
      );
      dependencies = deps;
    }

    // 마일스톤
    const [milestones] = await db.query(`
      SELECT m.*, pr.project_name, pr.job_no AS project_code
      FROM schedule_milestones m
      JOIN projects pr ON m.project_id = pr.project_id
      WHERE YEAR(m.milestone_date) = ?
      ORDER BY m.milestone_date
    `, [year]);

    return { entries, dependencies, milestones };
  },

  async createEntry(data) {
    const db = await getDb();
    const [result] = await db.query(
      `INSERT INTO schedule_entries
       (project_id, phase_id, task_name, start_date, end_date, progress, status, assignee, notes, display_order, created_by, source, work_type_id, risk_assessment_id)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [data.project_id, data.phase_id, data.task_name, data.start_date, data.end_date,
       data.progress || 0, data.status || 'planned', data.assignee || null,
       data.notes || null, data.display_order || 0, data.created_by || null,
       data.source || 'manual', data.work_type_id || null, data.risk_assessment_id || null]
    );
    return result.insertId;
  },

  async createBatchEntries(projectId, phaseId, entries, createdBy) {
    const db = await getDb();
    const ids = [];
    for (const entry of entries) {
      const [result] = await db.query(
        `INSERT INTO schedule_entries
         (project_id, phase_id, task_name, start_date, end_date, display_order, created_by)
         VALUES (?, ?, ?, ?, ?, ?, ?)`,
        [projectId, phaseId, entry.task_name, entry.start_date, entry.end_date,
         entry.display_order || 0, createdBy]
      );
      ids.push(result.insertId);
    }
    return ids;
  },

  async updateEntry(entryId, data) {
    const db = await getDb();
    const fields = [];
    const params = [];
    const allowed = ['task_name', 'start_date', 'end_date', 'progress', 'status', 'assignee', 'notes', 'display_order', 'phase_id', 'work_type_id', 'risk_assessment_id'];
    for (const key of allowed) {
      if (data[key] !== undefined) { fields.push(`${key} = ?`); params.push(data[key]); }
    }
    if (fields.length === 0) return;
    fields.push('updated_at = NOW()');
    params.push(entryId);
    await db.query(`UPDATE schedule_entries SET ${fields.join(', ')} WHERE entry_id = ?`, params);
  },

  async updateProgress(entryId, progress) {
    const db = await getDb();
    let status = 'in_progress';
    if (progress === 0) status = 'planned';
    if (progress === 100) status = 'completed';
    await db.query(
      'UPDATE schedule_entries SET progress = ?, status = ?, updated_at = NOW() WHERE entry_id = ?',
      [progress, status, entryId]
    );
  },

  async deleteEntry(entryId) {
    const db = await getDb();
    await db.query('DELETE FROM schedule_entries WHERE entry_id = ?', [entryId]);
  },

  // === 의존관계 ===
  async addDependency(entryId, dependsOnEntryId) {
    const db = await getDb();
    const [result] = await db.query(
      'INSERT IGNORE INTO schedule_entry_dependencies (entry_id, depends_on_entry_id) VALUES (?, ?)',
      [entryId, dependsOnEntryId]
    );
    return result.insertId;
  },

  async removeDependency(entryId, dependsOnEntryId) {
    const db = await getDb();
    await db.query(
      'DELETE FROM schedule_entry_dependencies WHERE entry_id = ? AND depends_on_entry_id = ?',
      [entryId, dependsOnEntryId]
    );
  },

  async getDependencies(entryId) {
    const db = await getDb();
    const [rows] = await db.query(`
      SELECT d.*, e.task_name AS depends_on_task_name
      FROM schedule_entry_dependencies d
      JOIN schedule_entries e ON d.depends_on_entry_id = e.entry_id
      WHERE d.entry_id = ?
    `, [entryId]);
    return rows;
  },

  // === 마일스톤 ===
  async getMilestones(filters = {}) {
    const db = await getDb();
    let sql = `
      SELECT m.*, pr.project_name, pr.job_no AS project_code, e.task_name AS entry_task_name,
             su.name AS created_by_name
      FROM schedule_milestones m
      JOIN projects pr ON m.project_id = pr.project_id
      LEFT JOIN schedule_entries e ON m.entry_id = e.entry_id
      LEFT JOIN sso_users su ON m.created_by = su.user_id
      WHERE 1=1
    `;
    const params = [];
    if (filters.project_id) { sql += ' AND m.project_id = ?'; params.push(filters.project_id); }
    sql += ' ORDER BY m.milestone_date';
    const [rows] = await db.query(sql, params);
    return rows;
  },

  async createMilestone(data) {
    const db = await getDb();
    const [result] = await db.query(
      `INSERT INTO schedule_milestones
       (project_id, entry_id, milestone_name, milestone_date, milestone_type, status, notes, created_by)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
      [data.project_id, data.entry_id || null, data.milestone_name, data.milestone_date,
       data.milestone_type || 'deadline', data.status || 'upcoming',
       data.notes || null, data.created_by || null]
    );
    return result.insertId;
  },

  async updateMilestone(milestoneId, data) {
    const db = await getDb();
    const fields = [];
    const params = [];
    const allowed = ['milestone_name', 'milestone_date', 'milestone_type', 'status', 'entry_id', 'notes'];
    for (const key of allowed) {
      if (data[key] !== undefined) { fields.push(`${key} = ?`); params.push(data[key]); }
    }
    if (fields.length === 0) return;
    fields.push('updated_at = NOW()');
    params.push(milestoneId);
    await db.query(`UPDATE schedule_milestones SET ${fields.join(', ')} WHERE milestone_id = ?`, params);
  },

  async deleteMilestone(milestoneId) {
    const db = await getDb();
    await db.query('DELETE FROM schedule_milestones WHERE milestone_id = ?', [milestoneId]);
  },

  // === 제품유형 ===
  async getProductTypes() {
    const db = await getDb();
    const [rows] = await db.query(
      'SELECT * FROM product_types WHERE is_active = TRUE ORDER BY display_order'
    );
    return rows;
  },

  // === 표준공정 자동 생성 ===
  async generateFromTemplate(projectId, productTypeCode, createdBy) {
    const db = await getDb();
    const conn = await db.getConnection();
    try {
      await conn.beginTransaction();

      // 1. 중복 체크
      const [existing] = await conn.query(
        "SELECT COUNT(*) AS cnt FROM schedule_entries WHERE project_id = ? AND source = 'template'",
        [projectId]
      );
      if (existing[0].cnt > 0) {
        await conn.rollback();
        return { error: '이미 표준공정이 생성되었습니다' };
      }

      // 2. product_type_id 조회
      const [ptRows] = await conn.query(
        'SELECT id FROM product_types WHERE code = ?', [productTypeCode]
      );
      if (ptRows.length === 0) {
        await conn.rollback();
        return { error: '존재하지 않는 제품유형입니다' };
      }
      const productTypeId = ptRows[0].id;

      // 3. tksafety risk_process_templates 조회
      const [templates] = await conn.query(
        'SELECT * FROM risk_process_templates WHERE product_type = ? ORDER BY display_order',
        [productTypeCode]
      );
      if (templates.length === 0) {
        await conn.rollback();
        return { error: '해당 제품유형의 공정 템플릿이 없습니다' };
      }

      // 4. 각 템플릿 → phase 매칭/생성 → entry 생성
      let createdCount = 0;
      for (const tmpl of templates) {
        // phase 매칭: 1순위 전용, 2순위 범용, 3순위 신규
        const [specificPhase] = await conn.query(
          'SELECT phase_id FROM schedule_phases WHERE phase_name = ? AND product_type_id = ?',
          [tmpl.process_name, productTypeId]
        );
        let phaseId;
        if (specificPhase.length > 0) {
          phaseId = specificPhase[0].phase_id;
        } else {
          const [genericPhase] = await conn.query(
            'SELECT phase_id FROM schedule_phases WHERE phase_name = ? AND product_type_id IS NULL',
            [tmpl.process_name]
          );
          if (genericPhase.length > 0) {
            phaseId = genericPhase[0].phase_id;
          } else {
            // 신규 phase 생성 (제품유형 전용)
            const [newPhase] = await conn.query(
              'INSERT INTO schedule_phases (phase_name, display_order, product_type_id) VALUES (?, ?, ?)',
              [tmpl.process_name, tmpl.display_order, productTypeId]
            );
            phaseId = newPhase.insertId;
          }
        }

        // entry 생성 (날짜 NULL — 관리자가 나중에 입력)
        await conn.query(
          `INSERT INTO schedule_entries
           (project_id, phase_id, task_name, start_date, end_date, status, progress, source, display_order, created_by)
           VALUES (?, ?, ?, NULL, NULL, 'planned', 0, 'template', ?, ?)`,
          [projectId, phaseId, tmpl.process_name, tmpl.display_order, createdBy]
        );
        createdCount++;
      }

      await conn.commit();
      return { created: createdCount };
    } catch (err) {
      await conn.rollback();
      throw err;
    } finally {
      conn.release();
    }
  },

  // === 부적합 연동 (격리 함수) ===
  // 향후 System3 API 호출로 전환 시 이 함수만 수정
  async getNonconformanceByProject(projectId) {
    const db = await getDb();
    const [rows] = await db.query(
      `SELECT id, category, description, review_status, responsible_department,
              expected_completion_date, actual_completion_date, report_date
       FROM qc_issues WHERE project_id = ? AND review_status != 'disposed'
       ORDER BY report_date DESC`,
      [projectId]
    );
    return rows;
  }
};

module.exports = ScheduleModel;